Copy data from one to another sheet when Row matches

[Sourav]

Hello,

I have two sheets - Sheet1 and Sheet2. Sheet 2 has 100 column names and contains all the column name of Sheet 1 (which has 60 columns). So rest 40 column in sheet 2 is of no use. Sheet 2 has only one column where data is available and rest should be copied from Sheet 1 one if it matches with the unique value and populates the entire data into the matched column.

For example i have attached a sample file. In this, if the "opportunity number" in sheet 2 is present in sheet 1, then it should pick all values for that opportunity number and paste in respective column in sheet 2.

Thanks in advance.

 

[Pavan.Sada.PS]

Hello Sourav,

Welcome to Chandoo.org...

Not sure about the data size.. if your data size is less then, Here is the formula solution to your sample file..

Paste this is Sheet2 B2 Cell and copy down and across

=OFFSET(Sheet1!$A$1,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)-1,MATCH(Sheet2!B$1,Sheet1!$1:$1,0)-1)

Regards,
Pavan S

 

[Marc L]

Hi !

This is a VBA code using Excel formula :​

Sub Demo()
                   Dim Rg1 As Range, Rg2 As Range
                   Set Rg1 = Sheet1.Cells(1).CurrentRegion.Rows
                       AD$ = Rg1.Item("2:" & Rg1.Count).Address(External:=True)
Application.ScreenUpdating = False

With Sheet2.Cells(1).CurrentRegion
    Set Rg2 = .Rows("2:" & .Rows.Count).Columns

    For C& = 2 To .Columns.Count
                       V = Application.Match(.Cells(C).Value, Rg1.Item(1), 0)
        If Not IsError(V) Then
                              V = "VLOOKUP(A2," & AD & "," & V & ",FALSE)"
            Rg2.Item(C).Formula = "=IF(ISNA(" & V & "),""""," & V & ")"
        End If
    Next
  
    Rg2.Formula = Rg2.Value
End With

Set Rg1 = Nothing:  Set Rg2 = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​