Combine and then compare 2 lists

I need to compare two price lists from 2 different merchants. While some items are common, some are offered only by Merchant 1 and some by only Merchant 2.

What I need to do, is create a third list that contains all the items offered by either merchant (a Union, so to say) and contains the prices of those items as offered by both merchants. Each list is in a separate sheet

Eg. If I have these two lists:

List 1
A -- $2.00
B -- $1.50
C -- $3.00

List 2
B -- $2.00
C -- $2.50
D -- $5.00
E -- $1.50

Then I need to create this list:
Comparison
A -- $2.00 -- NA
B -- $1.50 -- $2.00
C -- $3.00 -- $2.50
D -- ..NA.. -- $5.00
E -- ..NA.. -- $1.50

Any help would be appreciated. I can use 'Consolidate Data' to get min/max/sum/average of the value etc, but I just want to see both values side by side.

Thanks

first consolidate both the data in other sheet then remove duplicate values & use vlookup function based on list

removed ans .....

but I just want to see both values side by side.

it is possible to use consolidate function but your above condition will ont match , other wise get macro to be run for this

I suggest to create a unique list and then VLOOKUP-OR even better INDEX& MATCH to get your results..

Hi,

May be this file could help you. open the file and run the macro.

Originally Posted by shisa

Well, thanks. Though, INDEX&MATCH is doing the same thing as VLOOKUP, yes?

Btw, I tried using the method linked to create Unique List, and it didn't work, there was just no result except blank cells.

Formula in the link that i provided is an ARRAY formula. Did you confirm this as an ARRAY?



-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

I strongly believe that INDEX & MATCH is much better than VLOOKUP. See here why!!