+ Reply to Thread
Results 1 to 5 of 5

Sorting (or maybe filtering) worksheet with multiple data in cells

  1. #1
    Registered User
    Join Date
    11-01-2008
    Location
    Alpharetta, GA
    Posts
    6

    Sorting (or maybe filtering) worksheet with multiple data in cells

    Hi all, 1st post. I tried a seach but got to page six before my eyes crossed.

    Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:

    Date Name Craft Experience ...more info...

    If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)

    I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.

    Example (Excel 2003):

    1/1/08 Bubba EL 22
    3/1/08 Spike MW, EL 8
    8/1/08 Bill BM, MW 15
    11/1/08 Bob R, EL 12
    1/1/08 Dave R, BM 5

    If I wanted folks with EL experience, I would get the following list:

    1/1/08 Bubba EL 22
    3/1/08 Spike MW, EL 8
    11/1/08 Bob R, EL 12
    8/1/08 Bill BM, MW 15
    1/1/08 Dave R, BM 5

    Bubba, Spike and Bob, all with EL experience, are together.

    Optional extra: Bubba, Spike and Bob, all with EL experience, would be sorted by date (soonest first)

    The results can be on the same sheet, or on another sheet with just the desired folks listed.

    Thanks in advance,

    Dave
    Last edited by VBA Noob; 11-02-2008 at 05:38 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    If I inherited a sheet with an intrinsic design flaw, I would quickly fix it.

    Add three colums to the right of Craft can call call them EL and BM and MW. Let's assume those cells are:
    D1 = EL
    E1 = BM
    F1 = MW

    Now, in D2, find out if this craft applies by looking at C2:
    Please Login or Register  to view this content.
    In E2, similarly:
    Please Login or Register  to view this content.
    And in F2:
    Please Login or Register  to view this content.
    Now you have found all the active codes for this craftsman.

    Copy these three cells (D2:F2) down through the remaining 4000 rows and you have clean answers.

    Highlight this entire new range, copy it, paste special > VALUES and the formulas are replaced with the real values.

    Now you can delete the original CRAFT column (C) and you can now sort your data using all your normal sorting tools with no special stuff needed.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-01-2008
    Location
    Alpharetta, GA
    Posts
    6
    Don't disagree with the whole new sheet, but it wasn't really an issue until one of the PM's got efficient. Your solution is fine except that as the example I provided only three of the approximately 15 different crafts that call in.

    After further review... I am going to use the Filter feature, "Custom" option and use *MW* to display the row that has MW anywhere in the "Craft" column.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673
    If your approach does what you need, go for it.

    JBeaucaire's suggestion would take about two minutes to do for 15 (or 50) crafts. My one suggested change would be list the crafts across the top row (as he suggested), and in D2 and copy down and right,

    =IF(FIND(D$1, $C2), "x", "")

    Changing the dollar signs allows the formula to be copied without modification.

    Assuming the rightmost element is years experience, you could capture that in another column using

    =VALUE(RIGHT(TRIM(C2), 2))

    Then replace all the new columns with their values, and delete column C.

    End to end, less than two minutes.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Quote Originally Posted by shg View Post
    If your approach does what you need, go for it.

    JBeaucaire's suggestion would take about two minutes to do for 15 (or 50) crafts. My one suggested change would be list the crafts across the top row (as he suggested), and in D2 and copy down and right,

    =IF(FIND(D$1, $C2), "x", "")

    Changing the dollar signs allows the formula to be copied without modification.

    Assuming the rightmost element is years experience, you could capture that in another column using

    =VALUE(RIGHT(TRIM(C2), 2))

    Then replace all the new columns with their values, and delete column C.

    End to end, less than two minutes.
    Oh yeah! I love a quick clean fix. Nice extension.

+ 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