Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find Discrepancies between Worksheets
I have 2 worksheets that should be identical (merged cells, colors/borders,
number formats...), but when I try to paste values from one to the other, it kicks it out because merged cells are not identically sized. Obviously, there is some discrepancy in the thousands of cells of formatting. Is there a way to compare the 2 worksheets and come up with a list of what is different? It's a long story why I can't just overwrite the one, but please just trust that one sheet cannot be changed at all and the other is an output from a database that I do not have access to and so have to give the administrator a list of changes to be made. Thank you for any help! Erin |
#2
|
|||
|
|||
Find Discrepancies between Worksheets
Maybe you could use a program written by Myrna Larson and Bill Manville.
You can find a copy on Chip Pearson's site: http://www.cpearson.com/excel/whatsnew.htm look for compare.xla Erin wrote: I have 2 worksheets that should be identical (merged cells, colors/borders, number formats...), but when I try to paste values from one to the other, it kicks it out because merged cells are not identically sized. Obviously, there is some discrepancy in the thousands of cells of formatting. Is there a way to compare the 2 worksheets and come up with a list of what is different? It's a long story why I can't just overwrite the one, but please just trust that one sheet cannot be changed at all and the other is an output from a database that I do not have access to and so have to give the administrator a list of changes to be made. Thank you for any help! Erin -- Dave Peterson |
#3
|
|||
|
|||
Find Discrepancies between Worksheets
Wow - very cool, but I still need more information. This only checks values,
formulas and number formats...I need more. I think I could get away with this and a list of where merged cells don't line up, but colors/lines would also be very helpful. Thank you for your help! Believe me, I will keep this add-in handy! Erin "Dave Peterson" wrote: Maybe you could use a program written by Myrna Larson and Bill Manville. You can find a copy on Chip Pearson's site: http://www.cpearson.com/excel/whatsnew.htm look for compare.xla Erin wrote: I have 2 worksheets that should be identical (merged cells, colors/borders, number formats...), but when I try to paste values from one to the other, it kicks it out because merged cells are not identically sized. Obviously, there is some discrepancy in the thousands of cells of formatting. Is there a way to compare the 2 worksheets and come up with a list of what is different? It's a long story why I can't just overwrite the one, but please just trust that one sheet cannot be changed at all and the other is an output from a database that I do not have access to and so have to give the administrator a list of changes to be made. Thank you for any help! Erin -- Dave Peterson |
#4
|
|||
|
|||
Find Discrepancies between Worksheets
You can go through each cell looking for borders, but there are lots of things
that could change--color, weight, style, position (top/bottom/left/right/both inside diagonals). Way beyond my interest! But to just check merged cells, you could use something like: Option Explicit Sub testme() Dim rptWks As Worksheet Dim wks As Worksheet Dim oRow As Long Dim oCol As Long Dim iCtr As Long Dim wksNames As Variant Dim myCell As Range wksNames = Array("sheet1", "sheet2") Set rptWks = Worksheets.Add rptWks.Range("a1").Resize(1, 2).Value = wksNames oCol = 0 For iCtr = LBound(wksNames) To UBound(wksNames) oCol = oCol + 1 oRow = 1 Set wks = Worksheets(wksNames(iCtr)) For Each myCell In wks.UsedRange.Cells If myCell.MergeArea.Cells.Count 1 Then If myCell.MergeArea.Cells(1).Address = myCell.Address Then oRow = oRow + 1 rptWks.Cells(oRow, oCol).Value = myCell.MergeArea.Address End If End If Next myCell Next iCtr With rptWks .Columns(2).Insert .Range("b1").Value = "On: " & wksNames(UBound(wksNames)) .Range("d1").Value = "On: " & wksNames(LBound(wksNames)) .Range("B2:b" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula _ = "=isnumber(match(a2,c:c,0))" .Range("d2:d" & .Cells(.Rows.Count, "c").End(xlUp).Row).Formula _ = "=isnumber(match(a2,a:a,0))" End With End Sub Erin wrote: Wow - very cool, but I still need more information. This only checks values, formulas and number formats...I need more. I think I could get away with this and a list of where merged cells don't line up, but colors/lines would also be very helpful. Thank you for your help! Believe me, I will keep this add-in handy! Erin "Dave Peterson" wrote: Maybe you could use a program written by Myrna Larson and Bill Manville. You can find a copy on Chip Pearson's site: http://www.cpearson.com/excel/whatsnew.htm look for compare.xla Erin wrote: I have 2 worksheets that should be identical (merged cells, colors/borders, number formats...), but when I try to paste values from one to the other, it kicks it out because merged cells are not identically sized. Obviously, there is some discrepancy in the thousands of cells of formatting. Is there a way to compare the 2 worksheets and come up with a list of what is different? It's a long story why I can't just overwrite the one, but please just trust that one sheet cannot be changed at all and the other is an output from a database that I do not have access to and so have to give the administrator a list of changes to be made. Thank you for any help! Erin -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Print the Results of a FIND ALL from multiple worksheets? | Excel Worksheet Functions | |||
Spawning worksheets and a summary per worksheet | Excel Discussion (Misc queries) | |||
getting a formula to find named worksheets in another? | Excel Worksheet Functions | |||
How do I compare 2 worksheets, 1 old, 1 updated to find difference | Excel Discussion (Misc queries) | |||
Protect/unprotect all worksheets | Excel Worksheet Functions |