# Help in comparing data

#### pcorpz

##### Active Member
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!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### jbeaucaire

##### Well-known Member
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
ABCD
1Product CodeDescriptionWH1 costWH1 Retail
210001Apples\$ * 12.50\$ * * * 15.00
310002Oranges\$ * 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
ABCDEFGH
1Product CodeDescriptionWH1 costWH1 RetailWH2 CostWH2 retailWH3 CostWH3 Retail
210001Apples\$ 12.50\$ 15.00\$ 11.25\$ 15.00\$ 14.00\$ 16.50
310002Oranges\$ 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.

Last edited:

Replies
2
Views
641
Replies
2
Views
287
Replies
2
Views
1K
Replies
9
Views
586
Replies
1
Views
179

1,190,653
Messages
5,982,126
Members
439,756
Latest member
alice128

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back