Allocating info

[MikeNZ]

I have two worksheets, 1 and 2. Worksheet 1 contains data exported from a seperate program that I can't alter. In both worksheets column A is labeled "Name", column B is "Skill 1", column C is "Skill 2" and column C is "Skill 4".

When the data is exported to worksheet 1 skills will always start in skill 1 even though the corresponding name does not have that skill. Is there a way that when the info is transferred to worksheet 2 that the skills will be in the correct column?

[zbor]

Can you upload some dummy example?

[MikeNZ]

How do I do that?

[MikeNZ]

Hopefully this worked. Worksheet 2 is how I need it to end up.

[ChemistB]

Okay, here's what I came up with.
First, instead of the headers Skill1, 2, etc,on sheet 2 I changed them to what you are expecting in sheet 1 so that I can directly refer to that in my Indexing and Matching (e.g. aa, bb, cc).

Here is the formula (In H4) based on the tables in your example

Please Login or Register  to view this content.

It's a pretty complex formula but it works. (See attachment) To make it less complex for your workbook (esp. since you'll need to include Sheet1! and Sheet2!), I'd use assigned names on as many fixed ranges as you can (e.g. Sheet1!A4:A9, Sheet1!B4:D9). Hope this helps.

[martindwilson]

if its as simplistic as that ie names are always in the same order and skill 1 is always the same code
with codes above the skill number in h2:j2
=IF(ISNUMBER(MATCH(Sheet1!H$2,$B4:$D4,0)),Sheet1!H$2,"") in h4 dragged across and down