Excel question, need some help.
Discussion
I have 2 worksheets, A and B.
They should be idnetical bar column J.
They are 1000s of rows and 60 columns wide so a manual look wont suffice.
I have looked on the net for a worksheet to worksheet compare and Im not willing to admit its either £50 to buy or a bit of VBA to get it running (im on Excel 2003 SP2 btw).
So does anyone have a way that I can either create a worksheet 3 that has the sections in contention highlighted? Or some other method of telling me what is different?
Or is it resorting to:
Sub CompareWorkSheets()
Dim c1 As Range
Dim c2 As Range
Dim RowPos As Long
Sheet3.Range("A1") = "Row"
Sheet3.Range("B1") = "Column"
Sheet3.Range("C1") = "Sheet1"
Sheet3.Range("D1") = "Sheet2"
RowPos = 1
For Each c1 In Sheet1.Range("A1:IV65535") '<<<Change range as required
If c1 <> Sheet2.Cells(c1.Row, c1.Column) Then
' Record difference
RowPos = RowPos + 1
Sheet3.Cells(RowPos, 1) = c1.Row
Sheet3.Cells(RowPos, 2) = c1.Column
Sheet3.Cells(RowPos, 3) = c1
Sheet3.Cells(RowPos, 4) = Sheet2.Cells(c1.Row, c1.Column)
MsgBox "Found"
End If
Next c1
End Sub
Ta for the help
They should be idnetical bar column J.
They are 1000s of rows and 60 columns wide so a manual look wont suffice.
I have looked on the net for a worksheet to worksheet compare and Im not willing to admit its either £50 to buy or a bit of VBA to get it running (im on Excel 2003 SP2 btw).
So does anyone have a way that I can either create a worksheet 3 that has the sections in contention highlighted? Or some other method of telling me what is different?
Or is it resorting to:
Sub CompareWorkSheets()
Dim c1 As Range
Dim c2 As Range
Dim RowPos As Long
Sheet3.Range("A1") = "Row"
Sheet3.Range("B1") = "Column"
Sheet3.Range("C1") = "Sheet1"
Sheet3.Range("D1") = "Sheet2"
RowPos = 1
For Each c1 In Sheet1.Range("A1:IV65535") '<<<Change range as required
If c1 <> Sheet2.Cells(c1.Row, c1.Column) Then
' Record difference
RowPos = RowPos + 1
Sheet3.Cells(RowPos, 1) = c1.Row
Sheet3.Cells(RowPos, 2) = c1.Column
Sheet3.Cells(RowPos, 3) = c1
Sheet3.Cells(RowPos, 4) = Sheet2.Cells(c1.Row, c1.Column)
MsgBox "Found"
End If
Next c1
End Sub
Ta for the help
On way of doing it without using VBA is on the third sheet do a formula of =IF(Sheet1!A1<>Sheet2!A1,"DIFF","") and then just copy it across the whole sheet and look at the DIFFs.
That'll give you an idea of how many differences there are at least, then you can decide to look at them manually or not. Your VBA looks OK, but I'd be interested in the number of differences first. Although, since you've already written the VBA...
Will it raise a MSGBOX for every difference found? - that could get annoying if there are lots of differences. Would be better if you just raised one MSGBOX at the end.
That'll give you an idea of how many differences there are at least, then you can decide to look at them manually or not. Your VBA looks OK, but I'd be interested in the number of differences first. Although, since you've already written the VBA...
Will it raise a MSGBOX for every difference found? - that could get annoying if there are lots of differences. Would be better if you just raised one MSGBOX at the end.
Edited by ewenm on Wednesday 24th February 10:50
ewenm said:
On way of doing it without using VBA is on the third sheet do a formula of =IF(Sheet1!A1<>Sheet2!A1,"DIFF","") and then just copy it across the whole sheet and look at the DIFFs.
That'll give you an idea of how many differences there are at least, then you can decide to look at them manually or not.
What he said and also you could use conditional formatting to set the colour of the cell to (maybe) red to its easier to see and also change the "DIFF" to actually show the two values together, something like:That'll give you an idea of how many differences there are at least, then you can decide to look at them manually or not.
=IF(Sheet1!A1<>Sheet2!A1, Sheet1!A1 & " | " & Sheet2!A1,"")
ewenm said:
I don't get what you mean by "highlighting the section of the formula".
If the Cell Sheet1:M1 is different to Sheet2:M1 in Worksheet3 I want to have the Sheet1:M1 => Sheet2:M1 which would look like:0 => 100 as it does, but I want to highlight it in yellow so it is easily identifiable.
Just the cells that are not the same though
Dupont666 said:
ewenm said:
I don't get what you mean by "highlighting the section of the formula".
If the Cell Sheet1:M1 is different to Sheet2:M1 in Worksheet3 I want to have the Sheet1:M1 => Sheet2:M1 which would look like:0 => 100 as it does, but I want to highlight it in yellow so it is easily identifiable.
Just the cells that are not the same though
Cuchillo said:
Dupont666 said:
ewenm said:
I don't get what you mean by "highlighting the section of the formula".
If the Cell Sheet1:M1 is different to Sheet2:M1 in Worksheet3 I want to have the Sheet1:M1 => Sheet2:M1 which would look like:0 => 100 as it does, but I want to highlight it in yellow so it is easily identifiable.
Just the cells that are not the same though
Tried that first, thought I might be able to sneakily check in the formula as a defined name and then use the conditional formatting referencing to the defined name and see what happens but I have no time to test all that.
Cuchillo said:
Dupont666 said:
ewenm said:
I don't get what you mean by "highlighting the section of the formula".
If the Cell Sheet1:M1 is different to Sheet2:M1 in Worksheet3 I want to have the Sheet1:M1 => Sheet2:M1 which would look like:0 => 100 as it does, but I want to highlight it in yellow so it is easily identifiable.
Just the cells that are not the same though
Set the conditional formatting on Sheet3 to look for "=>" and highlight the cell yellow.
Edited by ewenm on Wednesday 24th February 11:24
ewenm said:
Cuchillo said:
Dupont666 said:
ewenm said:
I don't get what you mean by "highlighting the section of the formula".
If the Cell Sheet1:M1 is different to Sheet2:M1 in Worksheet3 I want to have the Sheet1:M1 => Sheet2:M1 which would look like:0 => 100 as it does, but I want to highlight it in yellow so it is easily identifiable.
Just the cells that are not the same though
Set the conditional formatting on Sheet3 to look for "=>" and highlight the cell yellow.
Edited by ewenm on Wednesday 24th February 11:24
=IF(ISNUMBER(SEARCH("*=>*",A1)),"=>","")
would that work or would it just tell them all to look at A1 for =>?
Dupont666 said:
ewenm said:
Cuchillo said:
Dupont666 said:
ewenm said:
I don't get what you mean by "highlighting the section of the formula".
If the Cell Sheet1:M1 is different to Sheet2:M1 in Worksheet3 I want to have the Sheet1:M1 => Sheet2:M1 which would look like:0 => 100 as it does, but I want to highlight it in yellow so it is easily identifiable.
Just the cells that are not the same though
Set the conditional formatting on Sheet3 to look for "=>" and highlight the cell yellow.
Edited by ewenm on Wednesday 24th February 11:24
=IF(ISNUMBER(SEARCH("*=>*",A1)),"=>","")
would that work or would it just tell them all to look at A1 for =>?
Formula Is =A1<>""
Then set the Cell Pattern to Yellow.
I then used the Format Painter to copy the formatting of Cell A1 and pasted it across all the cells on Sheet 3.
All the cells that had a value in it turned Yellow. The blank ones remains white.
NOTE: Make sure that the Formula is NOT =$A$1<>"" as Excel defaults to the absolute cell reference
HTH
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff