How to Mach the two sheet with common column?

AgKrishna

New member
Joined
Nov 25, 2014
Messages
1
Reaction score
0
Points
0
C1C2C3C4C1C5C1C5C2C3C4
AA22A33A44AA55AA55A22A33A44
BB22B33B44AA55AA55A22A33A44
CC22C33C44CC55CC55C22C33C44
DD22D33D44DD55DD55D22D33D44
EE22E33E44DD55DD55D22D33D44
FF22F33F44EE55EE55E22E33E44
FF55FF55F22F33F44
FF55FF55F22F33F44
FF55FF55F22F33F44
Sheet 1Sheet 2Sheet 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....
 
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
 
Hi AgKrishna,

pl find attached file.

@Bob Phillips Sir, Sorry for cross posting.
 

Attachments

  • solve.xlsx
    9.5 KB · Views: 14
Back
Top