Match two excel spread sheet using Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lillian Lian - 25 Sep 2007 00:34 GMT

I have one excel sheet, on sheet1 and sheet2 as following...

sheet1 has 1200 records
ColumnA ColumnB ColumnC
123 xxxx YYY
222 xxxx xxxxx

sheet2 has 1000 records
ColumnA ColumnB ColumnC
123 xxxx YYY
234 xxxx XXXXX
222 xxx xxxxx

if ColumnA of sheet1 and sheet2 are not match, like my example has 234, then
I would this row moved to sheet1 of column F, G, H.

Thanks.

Lillian

Reply to this Message

OssieMac - 25 Sep 2007 01:18 GMT

Hi Lillian,

Need a bit of confirmation.

What rows do you want the data moved to? Just starting at top row and all
the moved rows one under the other in columns F, G and H or do they have to
be placed relative to the last matched row?

Also, does columns F, G and H have column headers or can column headers be
inserted?

When you say 'moved', do you mean that they have to be cut from the original
list or just copied?

Regards,

OssieMac

Reply to this Message

Lillian Lian - 25 Sep 2007 15:46 GMT

OssieMac,

Thank you for help me out, acutally I want be like this

Sheet1 has 1000 records it has
SSN Name IPadress
123 linda 1.1.1.1
222 john 2.2.2.2

Sheet2 has 1200 records it has
SSN Name IPaddress
123 linda 1.1.1.1
222 john 2.2.2.2
333 bob 3.3.3.3

if A2 of sheet1 and sheet2 match then copy sheet1 of columA, columnB,
ColumnC to sheet2 of column F.G.H, so it would look like this on sheet2

SSN Name IPaddress ColumF ColumG ColumH
123 linda 1.1.1.1 123 linda 1.1.1.1
222 john 2.2.2.2 222 john 2.2.2.2
333 bob 3.3.3.3

that way I can see SSN 333 is not in the sheet1
I can see the match and not match both on same sheet 2.

> Hi Lillian,
>
[quoted text clipped - 13 lines]
>
> OssieMac

Reply to this Message

OssieMac - 26 Sep 2007 02:48 GMT

Hi Lillian,

I hope that what I have done will do the trick for you. However, make sure
that you back up your workbook before installing and running it just in case
it does not do what you expect.

I have also included a second macro (see comments at top of macro) which
will perform in the reverse just in case you have any data on the first sheet
that does not appear on the second sheet.

Sub Compare_Copy_1()

'This macro as per you request

Dim rngSht1 As Range
Dim rngSht2 As Range
Dim foundCell As Range

With Sheets("Sheet1")
Set rngSht1 = Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With

With Sheets("Sheet2")
Set rngSht2 = Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With

For Each c In rngSht2
Set foundCell = rngSht1.Find(What:=c.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundCell Is Nothing Then
Range(foundCell, rngSht1.Cells(foundCell.Row, 3)).Copy _
Destination:=c.Offset(0, 5)
Else
'Following line just in case data left in cells
Range(c.Offset(0, 5), c.Offset(0, 8)) = ""
End If
Next c

End Sub

Sub Compare_Copy_2()

'This macro works in reverse and copies matching
'data to sheet1 in case you have any data in sheet1
'that is not on sheet2

Dim rngSht2 As Range
Dim rngSht1 As Range
Dim foundCell As Range

With Sheets("Sheet2")
Set rngSht2 = Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With

With Sheets("Sheet1")
Set rngSht1 = Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With

For Each c In rngSht1
Set foundCell = rngSht2.Find(What:=c.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundCell Is Nothing Then
Range(foundCell, rngSht2.Cells(foundCell.Row, 3)).Copy _
Destination:=c.Offset(0, 5)
Else
'Following line just in case data left in cells
Range(c.Offset(0, 5), c.Offset(0, 8)) = ""
End If
Next c

End Sub

Regards,

OssieMac

> OssieMac,
>
[quoted text clipped - 39 lines]
> >
> > OssieMac

Reply to this Message