How to compare two Excel sheets with varying data

Solved/Closed
BWIRyan
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
- Sep 7, 2015 at 09:22 PM
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
- Feb 18, 2016 at 11:10 AM
Hello,
Before I start let me just say Thanks in advance.

So I am looking for an ongoing updated answer. Every 3 months we receive a new price list from our suppliers. Column A is an item number that stays true over every update, but the list price Column D for each row may or may not change from tri month to month. I there a way to setup a master workbook, so it will read the data for each Item number across sheet 1 & 2 and tell me if it has changed. There are roughly 1100 products listed every 3 months, and a simple way of seeing if data in column D matches column A over the 2 sheets would make this process of finding changes and not having to search every product every 3 months a lot easier.
I'll include an example below.

Sheet 1- VALID010415_030315
Item Number| Description | List Price |
#30 | Product | 0.50|
Sheet 2- VALID010915_030915
Item Number | Description | List Price |
#30 | Product | 0.55|

Col A is Item Number
Col B is description
Col D is list price.

I only need to know if the Col D has changed since previous sheet, but if new products are added or some removed, if would be helpful to have something alerting me of these occurrences.


Example If
Sheet 1- VALID010415_030315
A4| Item Number| Description| List Price|
A35| #30 | Product | 0.50|

But on Sheet 2
Sheet 2- VALID010915_030915
A4| Item Number | Description | List Price |
A49 | #30 | Product | 0.55|
Because some products have changed by way of addition or subtraction I kinda need to know so I dont take the cells of Col D and their values and adjust all prices incorrectly.

Is this making sense to anyone. I'm struggling to Explain?
I am very appreciative of anyone who can assist with my dilemma in advance, And I look forward to any ongoing feedback you may provide.


Related:

6 replies

TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Sep 8, 2015 at 11:14 AM
Hi Ryan,

So sheet1 has the old prices and sheet2 has the updated prices. Then why is sheet1 still relevant?

A solution could be to create a new sheet (Master sheet). Copy the products from both sheet to this master sheet. Then remove the duplicates and use VLOOKUP to retrieve the prices. Put the prices from sheet1 in a different column then the prices from sheet2. Then use conditional format to highlight differences in prices.

Sheet1:
Item | Price
#1 | 0.50
#2 | 0.75

Sheet2:
#1 | 0.55
#2 | 0.75

Master Sheet:
#1 | 0.50 | 0.55 <-- Here is a difference so CF will highlight this row.
#2 | 0.75 | 0.75 <-- No difference here.

Is this a solution that could work for you?
Can you make this work?

Best regards,
Trowa
5
BWIRyan
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
4
Sep 8, 2015 at 07:59 PM
Dear Towra,

Thank you for your prompt response.

You asked "So sheet1 has the old prices and sheet2 has the updated prices. Then why is sheet1 still relevant?"

It's relavant because if the value in the Column A on sheet 1 matches Sheet 2, Then I need to see if the valve in Column D has changed or stay the same.

We are finding not all products are changing in price. I cant just take the new spreadsheet and upload it into our CRM/Products table, I Have to enter it manually, Thus why i'm trying to compare the data between old and new, So as to make my import across policy a little easier, God Help me if they have all changed..

I will try what you have suggested and let you know, if your equation solves my riddle. Thanking you again. Ryan
0
BWIRyan
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015
4 > BWIRyan
Posts
6
Registration date
Monday September 7, 2015
Status
Member
Last seen
September 10, 2015

Sep 8, 2015 at 08:03 PM
Dear Towra,
I don't believe this can work. Because The supplier add and remove products from month to month. So if I "Put the prices from sheet1 in a different column then the prices from sheet2. " Unless I sit there and manually copy each line, ensuring its Col A data matches, I can't just copy the Col. without first knowing if The data matches. I'll upload part of the document in bow below so u can see what I mean.
Thanking you again for taking time to assist me.
0