# Thread: How to Mach the two sheet with common column?

1. ## How to Mach the two sheet with common column?

 C1 C2 C3 C4 C1 C5 C1 C5 C2 C3 C4 A A22 A33 A44 A A55 A A55 A22 A33 A44 B B22 B33 B44 A A55 A A55 A22 A33 A44 C C22 C33 C44 C C55 C C55 C22 C33 C44 D D22 D33 D44 D D55 D D55 D22 D33 D44 E E22 E33 E44 D D55 D D55 D22 D33 D44 F F22 F33 F44 E E55 E E55 E22 E33 E44 F F55 F F55 F22 F33 F44 F F55 F F55 F22 F33 F44 F F55 F F55 F22 F33 F44 Sheet 1 Sheet 2 Sheet 3

Hi ExcelGuru

See the Above Sheet1 and 2 and get Sheet 3

Sheet1 contain Col C1 as unique values
Sheet2 contain Col C1 as duplicate values

through the Sheet1 & Sheet2's Col C1 i want as result of Sheet3.

Pls help in that one....

2. Code:
```Public Sub Results()Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim lastrow As Long
Dim matchrow As Long
Dim lastcol1 As Long
Dim lastcol2 As Long
Dim i As Long

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")
With sh2

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastcol1 = sh1.Cells(1, sh1.Columns.Count).End(xlToLeft).Column
lastcol2 = .Cells(1, .Columns.Count).End(xlToLeft).Column
.UsedRange.Copy sh3.Range("A1")
sh1.Range("B1").Resize(, lastcol1 - 1).Copy sh3.Cells(1, lastcol2 + 1)
For i = 2 To lastrow

matchrow = Application.Match(.Cells(i, "A").Value, sh1.Columns(1), 0)
sh1.Cells(matchrow, "B").Resize(, lastcol1 - 1).Copy sh3.Cells(i, lastcol2 + 1)
Next i
End With
End Sub```

3. Hi AgKrishna,

pl find attached file.

@Bob Phillips Sir, Sorry for cross posting.