Getting similar data from 2 spreadsheets

[Man of Action]

Hi all,

Here is my problem:

I have two spreadsheets. Both pretty darn big. They both contain simialr data but not all is the same. What I want to do is search both spreadsheets and pick out the data that is the same on both sheets. Can anyone please offer some advice?

Thanks,
MOA


_________________
The internet! Is that thing still around?

[ This Message was edited by: Man of Action on 2002-05-22 12:39 ]

[ This Message was edited by: Man of Action on 2002-05-22 12:40 ]

[Man of Action]

Please someone help me!?!?!

[Al Chara]

Can you clarify for me...when you say data that is the same...do you mean that, for example, if sheet 1 A1 contains the same value as sheet 2 A1? Also, is your data all formulas, all values, or a mixture?

[Man of Action]

I am sorry for the confusion. The data is numbers. Some numbers are the same in both spreadsheets but not in the same orders. For example A1 on sheet 1 might be 2000 and A1 on sheet 2 might be 12843. But 2000 might not necessarily be on sheet 2. So I need a list of all the duplicates from both sheets.

Does this clarify?
MOA

[Damon Ostrander]

Hi MOA,

You didn't define what you mean by "pick out". If you just mean to highlight the cells that are the same, the following macro will do it. With minor modifications this macro could copy the common data to another sheet, or delete the non-common data from one of the sheets being tested.

Sub HighlightCommonData()
If ActiveWindow.SelectedSheets.Count <> 2 Then
MsgBox "Two sheets must be selected", vbExclamation, "Highlight Common Data"
Exit Sub
End If
Dim iRow As Long
Dim iCol As Integer
With ActiveWindow.SelectedSheets(1)
For iRow = .UsedRange.Row To .UsedRange.Row + .UsedRange.Rows.Count
For iCol = .UsedRange.Column To .UsedRange.Columns.Count
If Not IsEmpty(.Cells(iRow, iCol)) Then
If .Cells(iRow, iCol) = ActiveWindow.SelectedSheets(2).Cells(iRow, iCol) Then
.Cells(iRow, iCol).Interior.ColorIndex = 6
End If
End If
Next iCol
Next iRow
End With
End Sub

To use this macro, select the two sheets that you want to compare, then run the macro. The similarities will be highlighted on the active worksheet.

[Al Chara]

Are the ranges the same on both sheets? What are the ranges? Can there be duplicates?

[ This Message was edited by: Al Chara on 2002-05-22 13:29 ]

[Man of Action]

No the ranges are not the same. One is bigger that the other.

How do I modify the above code to copy the duplicates to a seperate spreadsheet?

[Asala42]

Hey,

If you can live with a formula approach (a bit more manual than VBA), you can probably go with a vlookup or countif. Copy this formula down a column adjacent to one of your lists:

=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"present","not present")

You can then apply a quick autofilter (for "present") and then copy/paste the visible cells to your other sheet.

Hope that helps,
Adam

[Al Chara]

MOA,

I had the same idea as Adam. If you want to do it in VBA then try the following code. Make sure you have a blank sheet 3. This is where the data will be copied.

I assumed that your list could expand past column A. If this is not true and your data only exists in Column A then I can simply the code for you. Repost if necessary.

Sub CheckWS()
Dim LargeRng As Range, SmallRng As Range
Set rng1 = Sheet1.UsedRange
Set rng2 = Sheet2.UsedRange
If rng1.Cells.Count > rng2.Cells.Count Then
Set LargeRng = rng1
Set SmallRng = rng2
Else:
Set LargeRng = rng2
Set SmallRng = rng1
End If

Sheet3.[a1].Formula = "=IF(COUNTIF(" & LargeRng.Worksheet.Name & _
"!" & LargeRng.Address & "," & SmallRng.Worksheet.Name & "!A1)>0," _
& SmallRng.Worksheet.Name & "!A1,"""")"

Sheet3.[a1].AutoFill Destination:=Sheet3.Range("a1:a" & LargeRng.Rows.Count), _
Type:=xlFillDefault

Sheet3.Range("a1:a" & LargeRng.Rows.Count).AutoFill Destination:=Sheet3.Range("a1:" & Cells(LargeRng.Rows.Count, LargeRng.Columns.Count).Address), _
Type:=xlFillDefault
End Sub