Solved

lookup then copy

Posted on 2015-02-20
13
53 Views
Last Modified: 2016-02-10
Could an expert provide me with VBA that will do the following:

Look up contents of cell A2 [sheet 1] in Sheet 2. column J

if it finds it in sheet 2 copy the entire rows into Sheet 3 [there could be just one row or many to copy]

So lets say in A2 in Sheet 1 we have CHF-38649188-LOH

look this up in Sheet 2 in column J

now lets say J10, J11,J12 all have ref CHF-38649188-LOH copy rows J10,J11J12 to Sheet3.

Continue doing this for all items in Sheet 1 column A
0
Comment
Question by:Jagwarman
  • 5
  • 4
  • 4
13 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40621274
Auto or Advanced Filter can do this for you.

Auto Filter will filter in place, Advanced Filter can be set to copy to another location.

When copying data from an Auto Filtered sheet, only visible rows will be copied and will paste as a contiguous block when pasted elsewhere.

Thanks
Rob H
0
 

Author Comment

by:Jagwarman
ID: 40621294
not sure I am following that. there are hundreds of items to look up and thousands of rows to look in
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40621335
For Auto Filter:

In sheet 1 put cursor in header row and select Filter from Data menu. This will add drop down boxes against each header. Select the dropdown for column J and enter your search criteria in the input box or select/deselect from the tick box list. The input criteria can be copied from another cell if you want.

Click OK and only those that match the criteria will be visible. If you now select this block of data and copy, when pasting elsewhere only those visible rows will be pasted.

For Advanced Filter:
Set up a small criteria table with a header matching the search column and the criteria immediately below. Multiple entries in one column, ie under one header will be treated as an OR comparison. Entries in the same row under multiple headers will be treated as an AND comparison.

The Advanced Filter function is on the Data menu as well. Select and a popup will come up with three entries required.

1) Data range - your data list
2) Criteria range - the small data table just setup
3) Copy to location - this is greyed out until you tick a box at the top of the popup. This copy to location has to be on the sheet from which you initiated the Adv Filter function. So if you want the data copied to a different sheet to the original data, select that sheet first. The copy to location also has to have a copy of the headers from the source data. The headers have to match the source data but do not have to include all headers or be in the same order. The data for headers not included in the Copy To location will not be copied.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40621362
could you post a sample workbook much easier
gowflow
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40621394
Just re-read the question and it looks like the Advanced Filter would be the way to go; the list of search values in Sheet 1 column A would be your criteria list. The header of this list has to match the header of the column in which you need to search for the value, column J if I read correctly.

Thanks
Rob H
0
 

Author Comment

by:Jagwarman
ID: 40621406
file attached
find-and-move.xlsx
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
ID: 40621420
So, in your example only rows 10 - 13 of sheet 2 would be copied as those IDs appear in the list on sheet 1.

Alternative Auto Filter option, use this formula in a spare column:

=IFERROR(MATCH(J2,Sheet1!$A$1:$A$43,0),"Hide")

Copied down for the extent of the data. You can then filter on this column to get rid of the Hide Values and then copy as before, only the visible rows will be copied.

Thanks
Rob H
0
 

Author Comment

by:Jagwarman
ID: 40621433
only 10 to 13 in my example but as I said previously there could be hundreds of different ones
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40621621
Shall we copy header of sheet2 to sheet3 ??
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40621653
Here it is and this is the code

Sub UpdateSheet3()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim Rng As Range, cCell As Range
Dim MaxRow1 As Long, I As Long, K As Long
Dim FirstAddress As String

Set WS1 = Sheets("Sheet1")
MaxRow1 = WS1.Range("A" & WS1.Rows.Count).End(xlUp).Row
Set WS2 = Sheets("Sheet2")
Set WS3 = Sheets("Sheet3")
WS3.Cells.Delete
WS2.Range("1:1").Copy WS3.Range("A1")
K = 2

For I = 2 To MaxRow1
    Set cCell = WS2.Range("J:J").Find(what:=WS1.Cells(I, "A"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    With WS2.Range("J:J")
        If Not cCell Is Nothing Then
            FirstAddress = cCell.Address
            Do
                If WS2.Cells(cCell.Row, "ZZ") = "" Then
                    WS2.Cells(cCell.Row, "A").EntireRow.Copy WS3.Range("A" & K)
                    K = K + 1
                    WS2.Cells(cCell.Row, "ZZ") = "Y"
                End If
                Set cCell = .FindNext(cCell)
            Loop While Not cCell Is Nothing And cCell.Address <> FirstAddress
        End If
    End With
    
Next I
WS2.Range("ZZ:ZZ").ClearContents
MsgBox "a total " & K - 1 & " rows from Sheet2 copied to Sheet3", vbExclamation
End Sub

Open in new window



gowflow
FmSheettoSheet-V01.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 400 total points
ID: 40621769
Sorry My first post was wrong I re-edited previous post that show new code.
here is the file again

gowflow
FmSheettoSheet-V01.xlsm
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40621863
Are you considering filter options as well?
0
 

Author Comment

by:Jagwarman
ID: 40625146
Rob I did like you solution but I really wanted VBA but thank you for your solution I am sure I will use that at some point in the future.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.

758 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

27 Experts available now in Live!

Get 1:1 Help Now