Excel question - simple?

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

  1. KevySaysBeNice

    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

    k1pp3r [H]ardness Supreme

    Messages:
    7,810
    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

    Druneau [H]ard|Gawd

    Messages:
    1,751
    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.
     
  4. Druneau

    Druneau [H]ard|Gawd

    Messages:
    1,751
    Joined:
    Jan 3, 2006
  5. KevySaysBeNice

    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

    Druneau [H]ard|Gawd

    Messages:
    1,751
    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...)