Merge two lists for side by side comparison

M

mfrye

I've attached a .jpg to the original message. I basically want to merg
my two lists based upon a unique identifier but want to maintain th
original coluumns of data associated with each idenitfier. I'v
entered as text below but I think it gets garbled when submitted t
forum.

List A
identifier Qty Cost
1 5 3.99
2 5 3.99
3 5 4.5
4 10 4.5
5 10 5

List B
Identifier Qty Cost
1 5 3.99
2 5 4.5
3 5 4.5
6 5 3.99
8 5 3.99

Combined List
Identifier qty from a cost from a qty from b cost from b
1 5 3.99 5 3.99
2 5 3.99 5 4.5
3 5 4.5 5 4.5
4 10 4.5
5 10 5
6 5 3.99
8 5 3.9
 
A

AlfD

Hi!

This gets close to what you describe.

First combine the two columns of identifiers into one containing uniqu
values. To do this, use Data> Filter >Advanced filter and put the resul
(remember to check unique values only) in a convenient place which ha
space for 4 columns alongside. This is the new identifier list and
will assume it is in column V from row 1 down.


Now I suggest (for convenience) you name your original 2 lists List
and List2. This includes all 3 columns in each case.

Alongside the new identifier list we want to use lookups from List1 an
List2 to populate the columns.
In W1 put
=IF(ISNA(VLOOKUP(V1,List1,2,FALSE)),"",VLOOKUP(V1,List1,2,FALSE)).
In X1, the same thing with 3 instead of 2 for the third argument.

Now repeat with List2 for Y1:
=IF(ISNA(VLOOKUP(V1,List2,2,FALSE)),"",VLOOKUP(V1,List2,2,FALSE))

and then with List2, column 3 (third argument) for Z1:
=IF(ISNA(VLOOKUP(V1,List2,3,FALSE)),"",VLOOKUP(V1,List2,3,FALSE))

Copy these formulae down as far as your V1 identifiers go.

This will list the items, but they will (unlike your illustration) b
in their strict columns and not "left- aligned". That could be achieve
by further manipulation but is it (a) needed; (b) worth it?

Al
 
Top