Matt Pinkston
asked on
Excel forumal to copy data from one tab to another when one column matches
I have an excel file with two tabs (Report & ABC)
Report is my main table and I would like to copy some fields into it from Tab ABC when there is a match on two columns...
Logic...
Formula
When value in column B (Report) is found anywhere in column A of (ABC)
Copy (ABC) Column C to (Report) Column U
Report is my main table and I would like to copy some fields into it from Tab ABC when there is a match on two columns...
Logic...
Formula
When value in column B (Report) is found anywhere in column A of (ABC)
Copy (ABC) Column C to (Report) Column U
Here's a macro you can use. If you need help implementing the macro please let me know.
Sub FindAndCopy()
Dim rngFound As Range
Dim lngLastRow As Long
Dim lngRow As Long
lngLastRow = Range("B1048576").End(xlUp).Row
Sheets("Report").Activate
For lngRow = 1 To lngLastRow
If Sheets("Report").Cells(lngRow, 2) <> "" Then
With Sheets("ABC").Range("A:A")
Set rngFound = .Cells.Find(What:=Sheets("Report").Cells(lngRow, 2), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not rngFound Is Nothing Then
If Sheets("ABC").Cells(rngFound.Row, 3) <> "" Then
Sheets("Report").Cells(lngRow, 21) = Sheets("ABC").Cells(rngFound.Row, 3)
Else
Sheets("Report").Cells(lngRow, 21) = "No Rating"
End If
Else
Sheets("Report").Cells(lngRow, 21) = "No Rating"
End If
End With
End If
Next
End Sub
If I have understood the requirement, I think you can use a VLOOKUP formula for this, in column U of Report:
=IF(VLOOKUP(Report!B1,ABC! A:C,3,FALS E)=0,"No Rating",VLOOKUP(Report!B1, ABC!A:C,3, FALSE))
Copied down as far as required.
Thanks
Rob H
=IF(VLOOKUP(Report!B1,ABC!
Copied down as far as required.
Thanks
Rob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rob H, like your solution but am getting a lot of
#NA and I would like this to say No Rating
#NA and I would like this to say No Rating
It could be the placing of brackets, I typed the formula, on the hoof as they say, rather than copying.
I am away from desk now but will take a look when get chance
I am away from desk now but will take a look when get chance
ASKER