Solved A Vlookup over multiple sheets and returning multiple values

Microsoft Excel 010 - complete package
February 15, 2012 at 07:15:00
Specs: Windows 7
I want to look up criteria over multiple sheets in Excel and return all the values of a cell that match that criteria. Similar to a Vlookup but over an undefined number sheets and to return multiple values. Can anyone help?

See More: A Vlookup over multiple sheets and returning multiple values

Report •

✔ Best Answer
February 16, 2012 at 20:27:47
This code assumes that the headings shown above begin in A1 in all sheets.

Paste the code into the Sheet Module for Sheet1 and then enter a tracking number in B2.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see if change was made to B2
  If Target.Address = "$B$2" Then
'If Yes, disable events
   Application.EnableEvents = False
'Store Track Number
    trackNum = Target
'Find last row in Sheet1
     endRow = Range("B" & Rows.Count).End(xlUp).Row
'Clear Sheet1
      Range("A2:I" & endRow).ClearContents
'Loop through Sheets
       For shtNum = 2 To Sheets.Count
'Find last row and begin search for Track number
        lastRow = Sheets(shtNum).Range("A" & Rows.Count).End(xlUp).Row
         With Sheets(shtNum).Range("A1:A" & lastRow)
          Set t = .Find(trackNum)
           If Not t Is Nothing Then
            firstAddress = t.Address
             Do
'For each Track number found, Copy data to next Row on Sheet1
               nxtRow = Range("B" & Rows.Count).End(xlUp).Row + 1
               Range("A" & nxtRow) = nxtRow - 1
               .Range("A" & t.Row & ":D" & t.Row).Copy _
                 Destination:=Range("B" & nxtRow)
               .Range("E" & t.Row).Copy _
                 Destination:=Range("G" & nxtRow)
               .Range("F" & t.Row).Copy _
                 Destination:=Range("F" & nxtRow)
               .Range("G" & t.Row & ":H" & t.Row).Copy _
                 Destination:=Range("H" & nxtRow)
               Set t = .FindNext(t)
             Loop While Not t Is Nothing And t.Address <> firstAddress
          End If
         End With
       Next
  End If
'Present message if Track number not found.
   Range(Target.Address) = trackNum
   If nxtRow = 0 Then MsgBox "Tracking Number Not Found"
'Re-Enable Events
   Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 15, 2012 at 08:13:52
This task is probably going to require some VBA (a macro) but we'll need some more details before we could offer any suggestions.

If you plan to post any example data, please click on the following line and read the instructions on how to post data in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 15, 2012 at 08:50:04
Line #	Track #	Cust PO #	Cust PO Line #	GR Reference	Module
1	E00748	CC056968	

This is in sheet one. I want to look up the Track # in the remaining sheets to populate the rest of the information. i.e. Cust PO#, Cust PO Line #, GR Reference, etc. How ever the track number may be in more than one sheet and I would like to return all the values that it finds.

Track ID   Client PO	Line Item 	GR #	   S/O Reference Module	  TAG ID  	     MATERIAL DESCRIPTION	QTY	UOM
E00700	   CC056968     3	    GR-WP106-001   = 10,652 FT		   347497-000	  XTV, 10XTV2-CT, SR CABLE      3247	M

The remaining sheets look like this and go to row 100.


Report •

#3
February 15, 2012 at 09:10:16
I'm not sure what you mean by "over an undefined number sheets".

Don't you know how many sheets are in your workbook?

I'm also confused about how often the Track number appears in the workbook. Does it only appear once and the problem is that you don't know which sheet it's in or does it appear in multiple sheets and you need to return multiple rows of data?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 15, 2012 at 09:14:08
As goods get recieved a new worksheet will be added. The track number may appear in multiple goods receipts and I want to return multiple rows of data. I hope this helps.

Report •

#5
February 15, 2012 at 09:56:39
There's more data/columns in the second set of example data than in the first.

What exactly do you want returned via the search?

The column headings are also confusing.

The first set shows:

GR Reference

The second shows:

GR #	   S/O Reference 

How do these relate?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 15, 2012 at 10:45:10
Here is the remaining coulmns from the first sheet. I thought I copied all of them.

Line #	Track #	Cust PO #	Cust PO Line #	GR Reference	Module	SO# /Del #	Tag ID	          Material Description
1	E00700	CC056968 	3	        GR-WP106-001     0	STRF-025	347497-000	XTV, 10XTV2-CT, SR CABLE

The GR Reference goes with the GR #
The SO#/Del # goes with the S/O Reference


Report •

#7
February 15, 2012 at 11:27:10
So Qty & UOM are not required to be copied to Sheet1?

Is the copied data appended to the end of the existing data on Sheet1 or are you starting with a blank table (other than the column headings) before each search?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 15, 2012 at 11:37:23
Sheet one is a information gathering template to enter a specific track number and get the different coulumns of information in order to determine which PO # , GR # etc it was recieved under in order to ship the correct product out.

Report •

#9
February 15, 2012 at 12:31:13
You didn't answer either of the 2 questions I asked in Response # 7.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 15, 2012 at 14:26:42
No QTY and UOM are not required to be copied.

I would start with a blank table and enter the track number to pull the info from the other sheets.


Report •

#11
February 16, 2012 at 06:27:02
This will take a little time, so I'll get back to you when I have something. Hopefully this evening, EST.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
February 16, 2012 at 20:27:47
✔ Best Answer
This code assumes that the headings shown above begin in A1 in all sheets.

Paste the code into the Sheet Module for Sheet1 and then enter a tracking number in B2.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see if change was made to B2
  If Target.Address = "$B$2" Then
'If Yes, disable events
   Application.EnableEvents = False
'Store Track Number
    trackNum = Target
'Find last row in Sheet1
     endRow = Range("B" & Rows.Count).End(xlUp).Row
'Clear Sheet1
      Range("A2:I" & endRow).ClearContents
'Loop through Sheets
       For shtNum = 2 To Sheets.Count
'Find last row and begin search for Track number
        lastRow = Sheets(shtNum).Range("A" & Rows.Count).End(xlUp).Row
         With Sheets(shtNum).Range("A1:A" & lastRow)
          Set t = .Find(trackNum)
           If Not t Is Nothing Then
            firstAddress = t.Address
             Do
'For each Track number found, Copy data to next Row on Sheet1
               nxtRow = Range("B" & Rows.Count).End(xlUp).Row + 1
               Range("A" & nxtRow) = nxtRow - 1
               .Range("A" & t.Row & ":D" & t.Row).Copy _
                 Destination:=Range("B" & nxtRow)
               .Range("E" & t.Row).Copy _
                 Destination:=Range("G" & nxtRow)
               .Range("F" & t.Row).Copy _
                 Destination:=Range("F" & nxtRow)
               .Range("G" & t.Row & ":H" & t.Row).Copy _
                 Destination:=Range("H" & nxtRow)
               Set t = .FindNext(t)
             Loop While Not t Is Nothing And t.Address <> firstAddress
          End If
         End With
       Next
  End If
'Present message if Track number not found.
   Range(Target.Address) = trackNum
   If nxtRow = 0 Then MsgBox "Tracking Number Not Found"
'Re-Enable Events
   Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question