Help in comparing data

[pcorpz]

Hi guys, I get three excel reports daily. The layout is the same for all three, they are just from different warehouses. Now, every week I to need combine these reports into one list to show changes within the last five days.

Basically, I need to consolidate similar items into just one line - for example, for the three warehouses -- the item Apple showed up in the reports, instead of putting Apple three times on the list, I would only put Apple once, and put in the column designated for Warehouse "ALL" (meaning it is changing for all warehouses). The exact match is when they are changing to the same cost and retail. Because there are items that may show up in all three warehouses, but with different costs and retails, in that case, I would need put that same item three times on the list, with the corresponding warehouse's cost and retail info.

I've been doing it the long way, buy comparing warehouse to warehouse, and do wish there is an easier way to accomplish this.

I tried playing around with pivot tables, although it shows me what items are similar and what items are not, I don't know how to extract or generate the list to how I want it to be without going through each line.

Hopefully someone might be able to give me some tips or help me out!

 

[jbeaucaire]

I would expect an INDEX / MATCH on each of the three separate sheets will accomplish what you want.

If the separate sheets were setup similar to this, with a unique code or description you could match:

Excel Workbook
	A	B	C	D
1	Product Code	Description	WH1 cost	WH1 Retail
2	10001	Apples	$ * 12.50	$ * * * 15.00
3	10002	Oranges	$ * 11.00	$ * * * 13.45
Sheet2

...then you could create a chart in a separate workbook that pulls values over by INDEXing and MATCHing on the closed workbook.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Product Code	Description	WH1 cost	WH1 Retail	WH2 Cost	WH2 retail	WH3 Cost	WH3 Retail
2	10001	Apples	$ 12.50	$ 15.00	$ 11.25	$ 15.00	$ 14.00	$ 16.50
3	10002	Oranges	$ 11.00	$ 13.45	$ 10.95	$ 14.00	$ 13.00	$ 15.00
Sheet1

Notice how the INDEX references include the full pathname to the external sheet, the bookname and sheet name? It added all those for me when i closed the book. All I did was have both books open when i created the original link like this:
=INDEX([Book1.xls]Sheet2!$C:$C,MATCH(Sheet1!$A2,[Book1.xls]Sheet2!$A:$A,0))

...and Excel did the rest.

With a layout like this, opening the sheet will automatically update this summary chart. Jsut keep save sheets from the warehouses in the same place each time.