Matching data from two different sheets

[bambi555]

Hi all,


Im trying to combine data from two separate data sets, one of which only has a subset of values from the other. Its a bit hard for me to explain so Ive attached an example. In sheet5 and sheet6 are the two data sets I have and in sheet7 is want I want to end up with. All the names with the same IDs in column B of sheet 6 should end up with the same ID from column B of sheet5.


Sorry if this is difficult to understand. Im sure there is some simple way to do this but I cant figure it out.


Thanks in advance for any help you can provide.

[sschwant]

It may be possible to do this using the PivotTable and PivotChart Wizard.

http://www.contextures.com/xlPivot08.html

http://answers.microsoft.com/en-us/o...b-0e87aff8148e

[offthelip]

I think you can get along way to where you want to go by using VlooKup,

As a start try these two vlookups on sheet 7.
in column E put;

=VLOOKUP(A1,Sheet6!A$1:B$7,2,FALSE)

copy this down,
In column F
put:
=VLOOKUP(Sheet6!A1,Sheet5!A$1:B$3,2,FALSE)
copy that down.

The names that do appear on sheet 5 will have values the others won't

You could then filter this list to just obtain the valid values and then do a final vlookup of the K value to get the C value.

I am not quite sure what your requirements are, so I don't know if this is sufficient

[p45cal]

In sheet6 (or a copy of it), in cell D1 enter:
=VLOOKUP(A1,Sheet5!$A$1:$B$3,2,FALSE)
and copy down. This is a helper column.

in sheet6 (or a copy of it) in cell C1 array-enter (that is Ctrl+Shift+Enter, NOT just Enter) this formula:
=INDEX($D$1:$D$7,MATCH(B1,IF(ISNA($D$1:$D$7),FALSE,$B$1:$B$7),0))
and copy down.

That's it.
To get rid of formulae and the helper column, copy/paste-special:values column C in situ, then delete column D.