Spreadsheet Comparison Closed

Question

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

Configuration: Windows 7 / Firefox 3.5.9

venkat1926 · Answer

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

DCecil07 · Answer

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.

venkat1926 · Answer

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.

DCecil07 · Answer

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.

venkat1926 · Answer

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.

Spreadsheet Comparison

5 responses

Similar discussions

Subscribe To Our Newsletter!