Compare Spreadsheets line by line

Gareth Davies

New Member
Joined
Oct 17, 2002
Messages
7
Hi, I have two large spreadsheets produced by the same system but on different dates. The second will have the same data as the first but with this weeks additions.
How do I compare the two sheets so that I can extract the changes.
In short I want to compare each line (not cell) on the first sheet with every line on the second sheet and either remove matches or highlight mismatches.
I've been playing with this idea for about three weeks but have got nowhere.
Thanks in advance, Gareth.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Noir

Active Member
Joined
Mar 24, 2002
Messages
362
When i tried this code (using the same Gareth Davies Old/New WB names just to be sure) the code erased everything from my new workbook. Matches as well as non-matches.

Noir
 

walkinjo

New Member
Joined
Oct 15, 2002
Messages
5
Here is a crazy idea:

On the new data, add a formula in the Y column that looks like this:

=A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1&Q1&R1&S1&T1&U1&V1&W1&X1

In row 1, then paste it down the sheet. Do the same thing on the old sheet.

Now in Z1 on the new sheet, use this formula:
=ISERROR(MATCH(Y1,[Oldsheet]!$Y$1:$Y$2000,0))

Where the [Oldsheet]!$Y$1:$Y$2000 would be replaced by the address of the new column you had just created on the old sheet.

Now the Z column will on the new sheet will show "TRUE" next to each row that does not exist on the old sheet, and false next to each row that exists on the old sheet.

You can use conditional formatting then to highlight the rows, if you want, or you can use an autofilter to filter out the FALSEs, and you will be left with the new ones.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

the fact that there will be at least 20000 rows makes this a little awkward (as you are going to have at least 40000 rows of calculations though admittedly they're not complex). I spoke to the guy about the macro as it will still be slow, best course would be to put the data into a proper database and query it from there, making it far quicker and far more robust.
 

grnseed

New Member
Joined
Feb 27, 2014
Messages
3
hello im wondering if you will help me. my boss dumped on me 2 of our slowest suppliers for products we use to assemble a final product. each supplier has over 400 parts that they supply us and we receive new stock from both 4 - 6 times per week. my boss wants me to go line item by line item every day to find slips in delivery, highlight the sku number and description and note how many days it is delayed at the end of the row also highlighted.

an example of our data sheet is as follows


MM670-35665-003FITTINGXXXXXX3/213/213/213/213/213/213/213/213/213/213/213/213/21
MM670-35655-003LOWER SILLXXXXXXXX3/43/43/43/43/43/43/43/43/43/4

<tbody>
</tbody>

PROBLEM IS IN THIS LINE OF DATA THERE ARE NUMEROUS ITEMS HIDDEN..
a part is assigned to a build not inventory as a whole .

what I need is a macro that will go line by line of unhidden data
if anything is different in anything UNHIDEN has changed to highlight the product number and description for me to review.

I receive 2 spread sheets daily

Can this be done??? Can someone help me ??
 

grnseed

New Member
Joined
Feb 27, 2014
Messages
3
Hi, I have two large spreadsheets produced by the same system but on different dates. The second will have the same data as the first but with this weeks additions.
How do I compare the two sheets so that I can extract the changes.
In short I want to compare each line (not cell) on the first sheet with every line on the second sheet and either remove matches or highlight mismatches.
I've been playing with this idea for about three weeks but have got nowhere.
Thanks in advance, Gareth.


Hi did you ever figure this out????
I have the same problem!!!!!
 

grnseed

New Member
Joined
Feb 27, 2014
Messages
3
how can I apply this function? I am completely lost my job just became this exact project with 3 suppliers and 6 sheets a day
I am about to quit I hate this so much PLEASE HELP. I DONT UNDERSTAND ... I am using excel 2013. I can donate to anyone who enables me to fix this within my computer.. I am running a parts received comparison. there are probably 6 columns that can impact negatively which need to be excluded

sheet 1
runs columns to AY on top
and down to 433

eed to avoid columns a, e-k
nd divdr lines
that seperarate the builds these parts are for
 

Forum statistics

Threads
1,148,181
Messages
5,745,204
Members
423,933
Latest member
ankushmukherjee

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