Spreadsheet Comparison

Closed
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010 - Apr 30, 2010 at 09:27 PM
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010 - May 7, 2010 at 09:51 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
May 4, 2010 at 09:07 PM
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
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
May 7, 2010 at 09:51 AM
Got it, Thanks.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 30, 2010 at 11:47 PM
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
0
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
May 3, 2010 at 12:52 PM
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.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
May 3, 2010 at 09:05 PM
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.
0

Didn't find the answer you are looking for?

Ask a question
DCecil07 Posts 30 Registration date Monday March 15, 2010 Status Member Last seen September 15, 2010
May 4, 2010 at 12:01 PM
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.
0