Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

lookup then copy

Posted on 2015-02-20
13
Medium Priority
?
63 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 34

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 34

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Expert Comment

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

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
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 400 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 31

Expert Comment

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

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 31

Accepted Solution

by:
gowflow earned 1600 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 34

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 to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

782 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