PDA

View Full Version : [SOLVED] Compare 2 sheets different sizes



ermis1975
06-15-2005, 03:26 PM
I want to compare 2 sheets. The problem is that the values are not in the same order. I have a date in the first cell (c1) and a value in the second cell (c2). The other sheet will contains also the same cells (c1), c2 but the number of values in each date may be more, or less. I want to compare the values for each date, and have the differences
Thanks

mdmackillop
06-15-2005, 03:43 PM
Hi Ermis,
Welcome to VBAX.
Can you post a sample of your data. You can do this clicking the Go Advanced button below the message box and using the Manage Attachment button on that page to send a zipped file. If you could also indicate the type of result you are looking for, I'm sure we can assist.
Regards
MD

ermis1975
06-16-2005, 03:31 AM
SHEET1SHEET2DATEVALUEDATEVALUE1/6/200532,131/6/2005401/6/200532,131/6/200532,
131/6/2005401/6/200547,61/6/2005401/6/200515,471/6/2005101,451/6/2005401/6/2005225,
151/6/2005225,151/6/200592,821/6/2005392,47

I want to compare sheet 1 with 2 and find the differences (highlighted them)

ermis1975
06-16-2005, 03:35 AM
oupss...
here is tha attachment..

austenr
06-16-2005, 07:21 AM
You might try this code:

http://www.exceltip.com/st/Compare_two_worksheet_ranges_using_VBA_in_Microsoft_Excel/476.html

austenr
06-16-2005, 07:45 AM
Or this:


=IF(COUNTIF(Sheet1!$A$2:$A$3,Sheet2!A2)>0,"Yes","No")

Place formula in C1 Sheet2 and copy down.

mdmackillop
06-16-2005, 03:27 PM
For a "highlighting" solution, try the following:


Option Explicit
Option Compare Text

Sub CompareSheets()
Dim Sh1Data(), SH2Data As String
Dim i As Long, j As Long, k As Long
Dim Match As Boolean
ReDim Sh1Data(100)
With Sheets("Sheet1")
For i = 1 To LastRow
j = j + 1
Sh1Data(i) = .Cells(i, 1) & .Cells(i, 2)
Next
End With
ReDim Preserve Sh1Data(j)
With Sheets("Sheet2")
For i = 1 To LastRow
Match = False
SH2Data = .Cells(i, 1) & .Cells(i, 2)
For k = 1 To j
Debug.Print SH2Data & " - " & Sh1Data(k)
If SH2Data = Sh1Data(k) Then
.Cells(i, 2).Interior.ColorIndex = 7
Match = True
GoTo skipped
End If
Next
If Not Match = True Then .Cells(i, 2).Interior.ColorIndex = 6
skipped:
Next
End With
End Sub

Function LastRow()
LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row()
End Function

ermis1975
06-16-2005, 03:35 PM
thanks to all!!

ermis1975
06-16-2005, 03:43 PM
And another question:
I want also to hightlight the values in the 1st sheet that doesnt exists in the second sheet!

Thanks a lot

austenr
06-16-2005, 03:47 PM
Gosh MD..Your solution is part of my next article for the KB!!! An example...thanks

mdmackillop
06-16-2005, 03:55 PM
Hi Ermis,
You could make a copy of the first sub and swap around the sheet references; 1 for 2 and 2 for 1, and call it from the first sub.
Hi Austen,

Maybe not the best example of Compare Text, just trying to avoid potential errors!

ermis1975
06-16-2005, 03:59 PM
Thanks...
I'll try it!

ermis1975
06-19-2005, 12:21 AM
Thanks its ok. But when the rows in sheet1 are more than th sheet 2 the matching stops, I have to return to sheet1 and run the marco again.
In this case it compares the 2 sheets, but it hightlights in sheet2 the same number of rows that contains sheet1.

ermis1975
07-05-2005, 02:32 PM
Is it possible to campare each value only one time? Eg. If in sheet1 I have 2 cells with the same number and in sheet2 only one then it hightlights all values and the oposite.
Thanks a lot!

mdmackillop
07-05-2005, 03:30 PM
The problem was that LastRow was only being determined on the sheet active when the macro was triggered. The following adjustment should sort it.



Option Explicit
Option Compare Text

Sub CompareSheets()
Dim SH1Data(), Sh2Data As String
Dim i As Long, j As Long, k As Long
Dim Match As Boolean
ReDim SH1Data(100)
Sheets("Sheet1").Activate
With ActiveSheet
For i = 1 To LastRow
j = j + 1
SH1Data(i) = .Cells(i, 1) & .Cells(i, 2)
Next
End With
ReDim Preserve SH1Data(j)
Sheets("Sheet2").Activate
With ActiveSheet
For i = 1 To LastRow
Match = False
Sh2Data = .Cells(i, 1) & .Cells(i, 2)
For k = 1 To j
Debug.Print Sh2Data & " - " & SH1Data(k)
If Sh2Data = SH1Data(k) Then
.Cells(i, 2).Interior.ColorIndex = 7
Match = True
GoTo skipped
End If
Next
If Not Match = True Then
.Cells(i, 2).Interior.ColorIndex = 6
End If
skipped:
Next
End With
CompareSheets2
End Sub

Sub CompareSheets2()
Dim Sh2Data(), SH1Data As String
Dim i As Long, j As Long, k As Long
Dim Match As Boolean
ReDim Sh2Data(100)
Sheets("Sheet2").Activate
With ActiveSheet
For i = 1 To LastRow
j = j + 1
Sh2Data(i) = .Cells(i, 1) & .Cells(i, 2)
Next
End With
ReDim Preserve Sh2Data(j)
Sheets("Sheet1").Activate
With ActiveSheet
For i = 1 To LastRow
Match = False
Debug.Print LastRow
SH1Data = .Cells(i, 1) & .Cells(i, 2)
For k = 1 To j
Debug.Print SH1Data & " - " & Sh2Data(k)
If SH1Data = Sh2Data(k) Then
.Cells(i, 2).Interior.ColorIndex = 7
Match = True
GoTo skipped
End If
Next
If Not Match = True Then
.Cells(i, 2).Interior.ColorIndex = 6
End If
skipped:
Next
End With
End Sub


Function LastRow()
LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row()
End Function