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

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.

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.

Originally Posted by shg

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.