Solved

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

Posted on 2015-02-09
6
89 Views
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...

Logic...

Formula
When value in column B (Report) is found anywhere in column A of (ABC)
Copy (ABC) Column C to (Report) Column U
0
Comment
Question by:Matt Pinkston
  • 3
  • 2
6 Comments
 

Author Comment

by:Matt Pinkston
Comment Utility
and if there is no find or the value in (ABC) column C is blank return a value of "No Rating"
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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.

Thanks
Rob H
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
Amendment to allow for No Find:

=IF(OR(ISERROR(VLOOKUP(Report!B1,ABC!A:C,3,FALSE)),VLOOKUP(Report!B1,ABC!A:C,3,FALSE)=0),"No Rating",VLOOKUP(Report!B1,ABC!A:C,3,FALSE))

Thanks
Rob H
0
 

Author Comment

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

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

Expert Comment

by:Rob Henson
Comment Utility
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now