match unique criteria and return entire row

[rsvinden]

I have two spreadsheets, Data & Return. In both worksheets Column A has a list of numbers. I want to match Data column A with Return Column A and return all data in that row to Return worksheet.

Both worksheets are large, over 50,000 rows.
Any help with this would be appreciated.

 

[John Davis]

I have two spreadsheets, Data & Return. In both worksheets Column A has a list of numbers. I want to match Data column A with Return Column A and return all data in that row to Return worksheet.

Both worksheets are large, over 50,000 rows.
Any help with this would be appreciated.

Sounds too me like VLOOKUP might be what you are looking for?

=VLOOKUP(A1,Data!$A$1:$Z$50000,2,false)

 

[rsvinden]

Thanks for the reply. Would this return all data in the row? This could be several columns.

 

[John Davis]

Thanks for the reply. Would this return all data in the row? This could be several columns.

This will return data from the second column as noted by the 2 in the formula. You'd have too have a separate formula for each column.

 

[rsvinden]

Thanks, I was trying to avoid the separate formulas for each col. The amount of data that needs to processed is huge. Hoping to cut down on the amount of work.
Thanks for your time and effort.

 

[John Davis]

Thanks, I was trying to avoid the separate formulas for each col. The amount of data that needs to processed is huge. Hoping to cut down on the amount of work.
Thanks for your time and effort.

You mention 50,000 rows. How many Columns do you have?

 

[rsvinden]

In the orig data file I have over 30 but I narrow this down to 11. Just being lazy.

 

[John Davis]

In the orig data file I have over 30 but I narrow this down to 11. Just being lazy.

If you put an absolute reference too the look up value in this case A1 to $A$1 and copy the formula accross, then edit/replace back to A1 all you'd have to do is for each formula change the pull column number (type 11 digits). Not alot of work. Then you could copy the row formulas down to 50000.

 

[rsvinden]

Thanks again. I appreciate the answer.

 

[stnkynts]

Maybe:

Sub MainMacro()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Data")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Return")
Dim iFind As Range, icell As Range
Dim lastrow As Long, lastrow2 As Long
 
lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
 
Application.ScreenUpdating = False
For Each icell In ws1.Range("A1:A" & lastrow)
    Set iFind = ws2.Range("A1:A" & lastrow2).Find(What:=icell, LookIn:=xlValues, LookAt:=xlWhole)
    If Not iFind Is Nothing Then
        icell.EntireRow.Copy Destination:=iFind
    End If
Next icell
Application.ScreenUpdating = True
 
End Sub