Help in comparing data

pcorpz

Active Member
Joined
Oct 29, 2004
Messages
324
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
Joined
May 8, 2002
Messages
6,012
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:
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top