Results 1 to 3 of 3

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

  1. #1

    How to Mach the two sheet with common column?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #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. #3
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi AgKrishna,

    pl find attached file.

    @Bob Phillips Sir, Sorry for cross posting.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •