# Excel question - simple?

Discussion in 'General Software' started by KevySaysBeNice, Dec 13, 2007.

1. ### KevySaysBeNice[H]ard|Gawd

Messages:
1,452
Joined:
Dec 7, 2001
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:

2. ### k1pp3r[H]ardness Supreme

Messages:
7,811
Joined:
Jun 16, 2004
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

3. ### Druneau[H]ard|Gawd

Messages:
1,745
Joined:
Jan 3, 2006
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.

Messages:
1,745
Joined:
Jan 3, 2006
5. ### KevySaysBeNice[H]ard|Gawd

Messages:
1,452
Joined:
Dec 7, 2001
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.

6. ### Druneau[H]ard|Gawd

Messages:
1,745
Joined:
Jan 3, 2006
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...)