Copy row from sheet1 to sheet 2 based on a matching number

[cores1805]

Hello

I need a formula which will copy a row of data from sheet1 to sheet2 if a number in column A on sheet2 matches a number on sheet1.

Basically, sheet1 of the excel sheet is used as a master list an sheet2 is a short list so i need to filter data through to the short list when needed.

Below is a link to the excel document I'm using, you should be able to edit it.

https://onedrive.live.com/redir?res...367&authkey=!AIUPhQqjoKRPdQ8&ithint=file,xlsx

If anyone can help it would be much appreciated.

 

[My Aswer Is This]

It's not possible as far as I know to copy a row of data from one sheet to another with a formula. You will need a Macro to do this.
If you want a Macro you need to explain exactly where the value is on the two sheets.
You said:
will copy a row of data from sheet1 to sheet2 if a number in column A on sheet2 matches a number on sheet1.
Where on sheet one are we looking for a value?
Please do not tell me to open your link. For security reasons I do not click on links.

 

[cores1805]

Basically, from cell 13 to 1000 on column A on sheet 1, I have a list of numbers, one per cell. What i want to happen is when i enter one of the numbers from column A on sheet1 into column A on sheet2, it copies the entire row of data that is on the same row as the number i entered. Hope this explains it.

 

[My Aswer Is This]

Thanks for that explanation. I will have to think some about how to do this.

 

[tonyyy]

cores1805,

You might try the following formula:

=IFERROR(OFFSET(INDIRECT("Sheet1!A" & MATCH($A1,Sheet1!$A$1:$A$1000,1)),0,COLUMN()-1),"")

The formula should be placed in cell B1 of Sheet2. Drag and copy the formula down for as many rows as you want, then drag and copy that selection to Column T.

The formula doesn't actually copy a row, but sets the value of each cell where a match is found to the corresponding cell from Sheet1.

Cheers,

tonyyy

p.s. And yes, this could be accomplished with a macro.

 

[MARK858]

@cores1805, a couple of macro interpretations are below but I must state that your file in your link is confusing.
In the file your numbers start in sheet1 from A2 yet in your post #3 you state they start in A13.
There is also no data in the columns to copy over, only formula which are returning blank cells.

As such the codes below both work on the entire Column A of sheet1 (change to a fixed range if needed)
The difference between the 2 codes is the first only brings over the values and the second copies everything.
The first is more efficient so use that if you can.

Both codes overwrite the formulas you have in sheet2.

Put 1 of the 2 codes in the sheet2 worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cRng As Range

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Column = 1 Then
        On Error Resume Next

        With Sheets("sheet1").Columns("A:A")
            Set cRng = .Find(What:=Target.Value, After:=.Cells(1, 1), LookIn:=xlValues, _
                             LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        End With

        With cRng.Offset(, 1).Resize(1, Sheets("sheet1").Cells(cRng.Row, Sheets("sheet1").Columns.Count - 1).End(xlToLeft).Column)
            Target.Offset(, 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With

        On Error GoTo 0

    End If


    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cRng As Range

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Column = 1 Then
        On Error Resume Next

        With Sheets("sheet1").Columns("A:A")
            Set cRng = .Find(What:=Target.Value, After:=.Cells(1, 1), LookIn:=xlValues, _
                             LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            cRng.Offset(, 1).Resize(1, Sheets("sheet1").Cells(cRng.Row, Sheets("sheet1").Columns.Count - 1).End(xlToLeft).Column).Copy Target.Offset(, 1)
        End With
        On Error GoTo 0

    End If


    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

I also note on your sample it is sheet1/sheet2 rather than the default Sheet1/Sheet2. Change the code if in reality this isn't the case.