A tricky situation of managing records in Excel

This is simply too much to take in from a pile of words...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Originally Posted by ArjunExcel

....
Issue-1: For example, an employee can do 12 types of projects.
Employee Name | Area of Expertise
John Doe | A, B, C, D, E, F, G, H, I, J, K, L

Do you see the problem? I have to write 12 types of expertise in a single cell.

1. Consider creating a table with skills across the top, names down the side and rates where applicable. DONT put all skills in 1 cell, you wont be able to do anything with them
2. Pay 1 of your guys to solve this for you? (just kidding, but it may be an option?)

I think you have a tiger by the tail.

If all jobs had equal duration and required only a single skill, then you would want to assign each to the person with that skill that results in the lowest total cost, without assigning two jobs to a single person -- kind of like choosing the best medley relay (swim) team. That's the "Assignment Problem". There are complex algorithms to solve it more efficiently than testing every combination, which would be a nonstarter. See, for example, http://www.cse.ust.hk/~golin/COMP572/Notes/Matching.pdf.

Now add to that the jobs surely have varying duration and require multiple skills.

I'm sure there is commercial software to do this, and expect it commands an admirable price.

First, you have to get the comma separated data into separate columns.

First do a global find and replace for ", " and replace with "," - you might also want to follow this up with replacing " ," with "," the purpose for this is to eliminate leading and trailing spaces.

The take the column with the comma, separated data and apply Data -> Text to columns to separate each skill into it's own column.

Next, you have to normalize the data. The word document describes the concept of normalized data. The spreadsheet. is an actual tool you can use to normalize the data. In Cell B1 enter the range of data you want to preserve. In your case it's A:A. In cell B2 enter the name of the sheet that contains the non-normalized source data. You can delete the data in the source data sheet and replace it with your own. In cell B3 enter in the name of the sheet you want the normalized data written to. You can keep the name Normalized Data. The program will overwrite what's there based on your data.

Then you can do the filtering the way you want using a pivot table.