+ Reply to Thread
Results 1 to 8 of 8

Sorting and filtering data

  1. #1
    Registered User
    Join Date
    10-28-2011
    Location
    Ontario
    MS-Off Ver
    Excel 2007
    Posts
    30

    Sorting and filtering data

    Hello everyone, could someone please tell me how do solve the following problem: I have two columns of numbers, column A ranging from 000 to 999 and column B contains the frequency of each of those numbers.

    Column A Column B

    000 2
    001 3
    etc.

    What I would like to do is sort the lists so that all the entries which contain the same three numbers and their frequencies are grouped together. For example, 012, 021, 102, 120, 201, 210 and their corresponding frequencies would make one group, 013, 031, 103, 130, 301, 310 and their corresponding frequencies would make another group, etc. It can be sorted either within the same column or another column. Is it possible to do this manually or would a macro be better. Thank you to anyone who can help me with this.
    Last edited by Omega71; 10-29-2011 at 11:59 AM.

  2. #2
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Sorting and filtering data

    I'm going to try and do this on the fly.
    copy these formulas down each column to the bottom of the data.
    Column D formula: "=If(Search(0,A1)>0,"0","1")"
    Column E formula: "=If(Search(1,A1)>0,"0","1")"
    .....
    Column M formula: "=(Search(9,A1))>0,"0","1")"

    Column N formula: "=D1&E1&F1.....&M1"
    This will producet these results in column N
    Please Login or Register  to view this content.
    Then when A1:N1000 is sorted on column N all the same digits will be together.
    (013 produces same as 310, 657 produces same as 756)
    It's not perfect, but it does group them together (555 will be after 756).

    If you need 555 to be before 756, it can probably be done, but I'd have to think about it.
    Last edited by foxguy; 10-28-2011 at 06:33 PM.
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  3. #3
    Registered User
    Join Date
    10-28-2011
    Location
    Ontario
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Sorting and filtering data

    Hey Foxguy, the formulas do not work. When I enter the formulas in their respective cells all I get in the cell is the formula. For example, in D column, when I enter the formula all I get in the cell is: "=If(Search(0,A1)>0,"0","1")" I tried it without the quotes at the beginning and end and now I get #VALUE! error. Could you please tell me what is wrong. Thanks for any help you can give.
    Last edited by Omega71; 10-29-2011 at 11:05 AM.

  4. #4
    Registered User
    Join Date
    09-22-2011
    Location
    Washington, USA
    MS-Off Ver
    Excel 2003,2007, 2010
    Posts
    85

    Re: Sorting and filtering data

    Did you put the " at the front of the formula? If so, then you created a text string. Take it out so that the formula starts with =

  5. #5
    Registered User
    Join Date
    10-28-2011
    Location
    Ontario
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Sorting and filtering data

    I tried it without the " and I still get the #VALUE! error.

  6. #6
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Sorting and filtering data

    Omega;
    You're right. I know I tested it and it worked, but it's not working now. I must have typed the formula different in my test, but I have no idea what it was.

    This works though.

    D1: =IF(SEARCH(0,A1&"0")<=LEN(A1),"0","1")
    E1: =IF(SEARCH(1,A1&"1")<=LEN(A1),"0","1")
    ....
    M1: =IF(SEARCH(9,A1&"9")<=LEN(A1),"0","1")

    N1: =D1&E1&F1.....&M1

    all copied down to the bottom of the data

  7. #7
    Registered User
    Join Date
    10-28-2011
    Location
    Ontario
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Sorting and filtering data

    Hey Foxguy, it worked perfectly. Thanks very much for your help. Take care.
    Last edited by Omega71; 10-29-2011 at 11:54 AM.

  8. #8
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Sorting and filtering data

    I don't understand what you mean by "don't come together".
    What range are you sorting? You should be sorting $A:$N on Column N (I think, I didn't test that part).
    Last edited by foxguy; 10-29-2011 at 11:57 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1