Comparing 4+ Lists of Data

[ml8]

I thought I knew where to start with this but sadly I it's been long enough to where I need some assistance. First of all what I am working with is four lists of product data that I need to compare against my own product data.


So for example each of the product lists contain the following data:


Product Category

Product Name

Inventory

Product Price


Each of the lists have at least a thousand records.


What I need is a fifth list that tells me which of the competitors products I don't have on my list.


Last but not least and where this gets trickier is that the inventory contains duplicate records.


Its hard for me even to construct this question as I spend time with it.


What I want though is a list of products that are shared by all of the competitors that I do NOT have on my product list.


Before I type anymore I am going to see if you're following me here.


Thanks for your time.


-Michael

 

[Hui]

Michael


Firstly, Welcome to the Chandoo.org Forums


I would do the following

If possible load the 4 lists into 1 sheet so that they are combined below each other

Add an ID Column Even if it is just the row number =Row()

Copy/Paste that column as values

Possibly add another column for Supplier so you know which Supplier it is from


Then just add a =Iferror(Match(Product name, Your Product Name Column, True),"No Match")


Product Name = Product Name Column from the competitors list


Then copy the formula down

Copy the Formula Column and Paste as values


Then sort the table by that formula column


All the entries with "No Match" are items your competitor have that you don't

 

[NARAYANK991]

Hi Michael ,


Can you upload your workbook ? Check out the following link for the way in which to do this :


http://chandoo.org/forums/topic/posting-a-sample-workbook


Narayan