Spreadsheet Comparison [Closed]

Report
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010
-
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010
-
I have a project that I am working on. Although the example uploaded is very simple, it's what I am looking for. I have a Master 'Sheet 1' and a Validation 'Sheet 2'. I will be pasting the data into the spreadsheet from other data spreadsheets. This is just the comparison that I need done. Once the Master and Validation is pasted into the spreadsheet, I would like the ability to click on a button and have results displayed on 'Sheet 3'. Sheet 3 will be an exact copy of Sheet 2 but will have the discrepancies highlighted in RED.

I'm not sure if it would be easier to have the discrepancies highlighted on Sheet 2 instead of even dealing with Sheet 3.

Let me know your thoughts! Thanks everyone.

https://authentification.site/files/22210066/Spreadsheet_Comparison.xlsx

5 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
796
my macro should work whatever the number of rows and columns provided there is not blank cell.

for j=1 to rlast
checks every row and within each row
for k=1 to clast
checks every column
that means every cell in the data base.
so it is not clear where the error occurs. how to help without seeing the organisation of your data. for security reasons can you not replace the data entries by fictitious data. and upload. small extract will do. full data base may not be necessary. configuration and not the values need be seen.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010

Got it, Thanks.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
796
I am afraid my previous reply was not recorded property. that is why this duplicate

try this macro (you have to use save the file as .xlsm)

Sub test()
Dim rlast As Integer, clast As Integer, j As Integer, k As Integer
Worksheets("sheet3").Activate
Cells.Interior.ColorIndex = xlNone

rlast = Range("A1").End(xlDown).Row
clast = Range("A1").End(xlToRight).Column

For j = 1 To rlast
For k = 1 To clast
If Worksheets("sheet3").Cells(j, k) <> Worksheets("sheet1").Cells(j, k) Then
Worksheets("sheet3").Cells(j, k).Interior.ColorIndex = 3
End If
Next k
Next j
End Sub
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010

For the code below, you have 'For J' and 'For K'. If the actual spreadsheet I am working with has to compare the entire sheet, how would I figure that into the code?

I am getting a 'Run-Time Error '6'' for this one. "Overflow"

Please help.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
796
it works in the sample spreadsheet you have given earlier. there was not bug.

can you upload your actual spreadsheet in
https://authentification.site
delete the password. and post the url where the sheet is uploaded.
Posts
30
Registration date
Monday March 15, 2010
Status
Member
Last seen
September 15, 2010

The spreadsheet that I provided was just an example. The actual data that I have contains personal data that I cannot post over the internet. The actual spreadsheet that I have contains more data that the sample given. It could contain more info at times too. I would need something that would take 'Sheet 1' and it's entirety as well as 'Sheet 2' and compare.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!