Excel question - simple?

K

KevySaysBeNice

[H]ard|Gawd
Joined
Dec 7, 2001
Messages
1,452
Hi, I have two questions, hardest question first:

I want to order documents in Excel based on how many times the element shows up in a column. For instance, here is a simple example:

Column A
--------------
1
1
1
2
3
3

I'd like to sort that column so that 2 is first (because there is only one 2), three is second (two 3s), and 1 is last (three 1s!).

Is there a way to do this?


Second question:

If I have an excel sheet with a bunch of blank rows, is there a way to automatically remove all of the blank rows and condense everything?


Thanks in advance for any help :heart:
 
Personally i would sort the column in accending order, then in say column C use the COUNT function to tell you how many 2's there are and how many 3's and so fourth
 
Kind of expanding on k1pp3r's suggestion...

Make a table with the possible values your data can be. Have a column beside it which will use "=COUNTIF(E4:E38,A5)" to count how many of a certain value. Then you can run a standard sort on the "count" column to sort your value column accordingly.
 
Druneau said:
Kind of expanding on k1pp3r's suggestion...

Make a table with the possible values your data can be. Have a column beside it which will use "=COUNTIF(E4:E38,A5)" to count how many of a certain value. Then you can run a standard sort on the "count" column to sort your value column accordingly.
Click to expand...

well, that's true, but i don't think that's really possible with 1,500 rows.


What I'm trying to do, basically, is take a bunch of data and eliminate every row where column A (actually a "Purchase Order") shows up only once. The rows where it shows up more than once can stay.
 
ok....


Then have =IF(COUNTIF($C$7:$C$40,C7)>1,C7,"") formula as a column.

This will get you the purchase order name only if it's found more than once in the data. If not, then you get a blank cell which you could remove using the method in the link i've provided. (I'm sure we could find a better way of removing those blank cells...)
 
You must log in or register to reply here.
Back
Top