Compare Spreadsheets line by line

[Gareth Davies]

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.

 

[gnaga]

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]

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]

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]

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.

 

[Gareth Davies]

The problem is tough enough without my spelling

 

[gnaga]

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]

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]

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]

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