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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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