Excel forumal to copy data from one tab to another when one column matches

Posted on 2015-02-09
Last Modified: 2015-02-09
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...


When value in column B (Report) is found anywhere in column A of (ABC)
Copy (ABC) Column C to (Report) Column U
Question by:Matt Pinkston
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Author Comment

by:Matt Pinkston
ID: 40598597
and if there is no find or the value in (ABC) column C is blank return a value of "No Rating"
LVL 47

Expert Comment

by:Martin Liss
ID: 40598759
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

    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)
                        Sheets("Report").Cells(lngRow, 21) = "No Rating"
                    End If
                    Sheets("Report").Cells(lngRow, 21) = "No Rating"
                End If
            End With
        End If
End Sub

Open in new window

LVL 33

Expert Comment

by:Rob Henson
ID: 40598907
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,FALSE)=0,"No Rating",VLOOKUP(Report!B1,ABC!A:C,3,FALSE))

Copied down as far as required.

Rob H
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

LVL 33

Accepted Solution

Rob Henson earned 500 total points
ID: 40598909
Amendment to allow for No Find:


Rob H

Author Comment

by:Matt Pinkston
ID: 40598927
Rob H, like your solution but am getting a lot of

#NA and I would like this to say No Rating
LVL 33

Expert Comment

by:Rob Henson
ID: 40599257
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question