+ Reply to Thread
Results 1 to 8 of 8

Data Sorting 101 Excel 2003

  1. #1
    Registered User
    Join Date
    12-15-2010
    Location
    Victoria BC
    MS-Off Ver
    Excel 2003
    Posts
    4

    Data Sorting 101 Excel 2003

    I have a spreadsheet, with multiple columns and several hundred rows. I want to find all instances of "D/C" that exist in the spreadsheet and have them grouped together, either in a new spreadsheet or in a grouping on the existing spreadsheet. The instances of "D/C" could be be in one of 3 different columns.

    I can use Find and search for "D/C" but that only points out where this "D/C" exists on the spreadsheet and doesn't let me identify this row for later sorting.

    Any other ideas?

    Bob

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Data Sorting 101 Excel 2003

    Hi bianson,

    I believe you need and Advanced Filter to do what you want.

    Read: http://www.ehow.com/how_4501379_set-...oft-excel.html

    and then search the net for Advanced Filter Examples.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    12-15-2010
    Location
    Victoria BC
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Data Sorting 101 Excel 2003

    Thanks MarvinP....I will give that a try.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Data Sorting 101 Excel 2003

    you can use advanced filter to filter all rows that contain d/c
    here is a small example. repeat all your column headings then in each one put d/c one row down
    from each other
    then data/ filter /advanced filter
    select range to be filtered then in criteria range select your newly created list see example
    list range is a1:c22
    criteria range is e1:g4
    when applied any row that has d/c will be shown
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    12-15-2010
    Location
    Victoria BC
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Data Sorting 101 Excel 2003

    Thanks for the advice so far. I tried the idea from martindwilson but I must be missing something.

    Here's what I have so far. What am I doing wrong?
    Attached Files Attached Files

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Data Sorting 101 Excel 2003

    you'd need to use wildcards in criteria as d/c isnt in cell on its own, but this method may not be needed,(but it wont hurt to learn it!)
    how many columns would you likely to be filtering ?in this example there are only 2 that have rows containing d/c so you could just join them together with =b3&d3 drag down to end then filter that column on custom/contains "d/c"
    attached shows set up for either method you can custom filter on col g or use advanced filter with h2:j5 as criteria

    dont forget when you use advanced filter as in this example as the data does not start in row 1 you must make sure the actual range is in the filter ie $A$2:$F$3302
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-15-2010
    Location
    Victoria BC
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Data Sorting 101 Excel 2003

    Martin,
    I am getting it but still a little thick this morning.

    In the example you sent DuvetCoveSort(2), the resulting spreradsheet shows the results in Column G labeled "helper".

    When I try and replicate the same thing for myself, it doesn't do the same thing.

    So I better understand, the wildcard *D/C* is created in columns H, I & J, with the same column headers as column B, C & D.

    The advanced filter says to use the List range of everything between B2 and the bottom of column D. The criteria range is the column headers and wildcards from H, I & J.

    What forces the output to column G? Just the fact that it's between the list and the criteria?

    When I replicate your process on the same data as I sent you, I get a single line of output. I am attaching the data with the new columns and headers created and another with the results. Can you tell me what I am doing wrong?

    Bob

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Data Sorting 101 Excel 2003

    you never included the column headers it should include row 3
    see this
    Attached Images Attached Images

+ 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