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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 ??
 
Upvote 0
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!!!!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top