multiple sheets if column a and b have same value then copy entire row into new sheet

hi everyone
would like some help with this.
have column a being an id and column b a date on multiple sheets.
if there is a match anywhere in the sheets then the macros would copy the entire row and place the two lines of data next to one another in the same row on a new sheet.

ex

sheet 1

mm2200 2/4/14 yes

sheet 2

mm2200 2/4/14 no

sheet 3

mm2200 2/4/14 yes mm2200 2/4/14 no


thanks!!

Can you provide a sample with some data to test on?

Hi John!

Thanks for getting back to me.
I have attached three files, the first two is the source data, the 3rd is what the macros would produce.

the macros is looking for a match in sheet1 columns a and b with sheet2 columns a and b, once a match is found it copies and pastes both rows and pastes adjacently into one row in the third file.

Ideally, the macros would do this for all matches in columns a and b checking the source data against each other, when a match is found pasting both rows into the 3rd sheet.

I have highlighted the example rows for ease of understanding.

THANKS!!!

hey john, let me know if you need anything else...would be a HUGE HELP!!

Maybe:

Sub myjebay1()
Dim i As Long
Dim rcell As Range
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Application.ScreenUpdating = False
Set ws = Workbooks("acasi ex.xlsx").Sheets("Sheet1")
Set ws2 = Workbooks("acasi ex2.xlsx").Sheets("Sheet1")
Set ws3 = Workbooks("acasi what it should look like.xlsx").Sheets("Sheet1")
ws.Activate
Columns(1).Insert
With Range("A2:A" & ws.UsedRange.Rows.count + 1)
    .Formula = "=CONCATENATE(B2,C2)"
    .Value = .Value
End With
ws2.Activate
Columns(1).Insert
With Range("A2:A" & ws.UsedRange.Rows.count + 1)
    .Formula = "=CONCATENATE(B2,C2)"
    .Value = .Value
End With
ws.Activate
For i = Range("AK" & Rows.count).End(3)(2).Row To 2 Step -1
    Range(Cells(i, "AL"), Cells(i, "AS")).FormulaArray = "=VLOOKUP(A" & i & ",'[acasi ex2.xlsx]Sheet1'!$A$2:$I$218,{2,3,4,5,6,7,8,9},FALSE)"
Next i
With Range("AL2:AS" & ActiveSheet.UsedRange.Rows.count)
    .Value = .Value
    .Replace "#N/A", ""
End With
For Each rcell In Range("AL2:AL" & ActiveSheet.UsedRange.Rows.count + 1)
    If rcell.Value <> "" Then
    Range(rcell.Offset(, -36), rcell.Offset(, 7)).Copy ws3.Range("A" & Rows.count).End(3)(2)
    End If
Next rcell
Range("AL2:AS" & ActiveSheet.UsedRange.Rows.count).ClearContents
Columns(1).Delete
ws2.Columns(1).Delete
Application.ScreenUpdating = True
End Sub

Based on your samples.

Wow! Thank you SO much! It works like a charm.
So my VBA skills are moderate at best. I thought when I saw the code I'd be able to tweak it to apply to Eight different spreadsheets. That unfortunately has not been the case.
Would you be able to walk me through it/be kind enough to come up with some code for it/want me to upload the other 6 spreadsheets?
It would literally be the same task but with 8 spreadsheets instead of two.

Thanks for all your help. You are a lifesaver.

Best Wishes,

8 Worksheets? Or 8 Workbooks?

8 workbooks

Originally Posted by myjebay1

8 workbooks

You want to compare each of those workbooks to this one?

Workbooks("acasi ex.xlsx").Sheets("Sheet1")

ideally it would compare all 8 to one another. anytime there was a match by ptid and date in the 8 it would CUT(sorry i put copy earlier, but if we cut it, we can see which ones do not have a match in the other workbooks and address them as missing)and paste.
therefore, if there were 6 matches it would take those 6 and place them in the new work book in a row, if there were 4 matches it would put those 4 in the same row, etc.

THANKS!

That's a bit more than I can help you with.

what if it were 8 worksheets? :/