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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
By writing VBA code can check the data. What kind of data you have in your sheet. Do you mean you have data only in column A is it number or text?

GNaga
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Also are the days additions added below the old data?

how many columns are we talking about?

i.e. are you wanting to compare

A1:Z1 on new to A1:Z1 on old etc?
 

Gareth Davies

New Member
Joined
Oct 17, 2002
Messages
7
Its a full spread, each sheet having about 20,000 lines of txt and numbers across to column X. You can see why I don't wan't to go throught it manually.
The cell content has no formatting so the content of the cells isn't important.
I'd realy like to be able to create a report of the new rows.
 

Gareth Davies

New Member
Joined
Oct 17, 2002
Messages
7

ADVERTISEMENT

The new data consists of new rows added between existing rows and old rows that have been updated.

Imagine a large worforce of engineers around the country all inputting thier work into a central database.
Some work will be new and some will be upgrades of existing customers.
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

Should I assume that you have data in Column A1 to Column Z20000. Each column have different data's like Date,Item1,Item2.....

So we have to compare sheet1 of A1:Z1 with sheet2 of A1:Z1. If any of the cell in sheet2 is different from sheet1 then mark the cell in sheet2 with different color.

Is the assumption it correct?

GNaga
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
I reckon you can do this, you will need a double loop.

The first loop will run through each TEXTSTRING of the new data...you will need to create a variable that merges the A:X (TEXTSTRING)

So TEXTSTRING = A1:X1 (where 1 will be a variable)

The second loop will loop through the old data strings and compare it to TEXTSTRING.

If you mail me some samples small I will write a quick macro that will you can then adapt to meet the specific requirements...

Luke
 

Gareth Davies

New Member
Joined
Oct 17, 2002
Messages
7
Gnaga: lasw10 has the right idea, the data must be dealt with on a row-by-row basis.
Although each row is different from the others within its own sheet, there are cells within other rows that are the same. (If that makes sense)

Lasw10: I'll E-mail you a "small" example.

Thanks for every-ones effort on this, I've spent three weeks on it but don't have the background knowledge to work it through.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
In case anyone was interested....

2 workbooks - New and Old.
This will create a string from the values in New and then compare it to the string created from the values in Old - cycling through Old. If no matches are found then it returns value "new" to NEW workbook and then starts again using the next string in NEW. If a match is found it will clear the repeated data from NEW



Sub COMPARISON()

Application.ScreenUpdating = False

Windows("GARETH_DAVIES_NEW.XLS").Activate
Sheets("SHEET1").Select

D1 = Sheets("SHEET1").Range("A2:A100")
DROW = 2

Windows("GARETH_DAVIES_OLD.XLS").Activate
Sheets("SHEET1").Activate
D2 = Sheets("SHEET1").Range("A2:A100")
CROW = 2

For Each ndata In D1

If ndata = "" Then Exit For

If ndata <> "" Then

For Each ODATA In D2

Windows("GARETH_DAVIES_NEW.XLS").Activate
Sheets("SHEET1").Select

TSTRING = Range("A" & DROW) & Range("B" & DROW) & Range("C" & DROW) & Range("D" & DROW) & Range("E" & DROW) & Range("F" & DROW) & Range("G" & DROW) & Range("H" & DROW) & Range("I" & DROW) & Range("J" & DROW) & Range("K" & DROW) & Range("L" & DROW) & Range("M" & DROW) & Range("N" & DROW) & Range("O" & DROW) & Range("P" & DROW) & Range("Q" & DROW) & Range("R" & DROW) & Range("S" & DROW) & Range("T" & DROW) & Range("U" & DROW) & Range("V" & DROW)

Windows("GARETH_DAVIES_OLD.XLS").Activate
Sheets("SHEET1").Select

TSTRING2 = Range("A" & CROW) & Range("B" & CROW) & Range("C" & CROW) & Range("D" & CROW) & Range("E" & CROW) & Range("F" & CROW) & Range("G" & CROW) & Range("H" & CROW) & Range("I" & CROW) & Range("J" & CROW) & Range("K" & CROW) & Range("L" & CROW) & Range("M" & CROW) & Range("N" & CROW) & Range("O" & CROW) & Range("P" & CROW) & Range("Q" & CROW) & Range("R" & CROW) & Range("S" & CROW) & Range("T" & CROW) & Range("U" & CROW) & Range("V" & CROW)

If TSTRING = TSTRING2 Then
Windows("GARETH_DAVIES_NEW.XLS").Activate
Sheets("SHEET1").Select
Range("A" & DROW & ":" & "W" & DROW).ClearContents
DROW = DROW + 1
CROW = 2
GoTo 99
End If

If TSTRING <> TSTRING2 And CROW = 100 Then
Windows("GARETH_DAVIES_NEW.XLS").Activate
Sheets("SHEET1").Select
Range("X" & DROW) = "NEW"
DROW = DROW + 1
CROW = 2
GoTo 99
End If

If TSTRING <> TSTRING2 Then CROW = CROW + 1

Next ODATA

End If

99

Next ndata



End Sub
 

Forum statistics

Threads
1,144,741
Messages
5,726,016
Members
422,653
Latest member
mntsiki

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