Can vlookup be used to retrieve multiple matches

  • Thread starter Thread starter jtravis432
  • Start date Start date
J

jtravis432

Say, I have a worksheet that has Column A for work orders, Column B
for parts

A work order may contain many parts on different rows:

Column A Column B
WO123 PN5
WO123 PN6
WO123 PN7
WO121 PN9
WO122 PN4
WO122 PN7
etc

I want to automatically "look up" a work order and "import" the parts
used into another worksheet - to create a "flat/horizontal" statement
like "WO122 used PN4 and PN7". How do I do this?
 
Try to use Autofilter.
Data>Filter>Advance Filter>Check mark on Unique Record Only.

See more info in the HELP.

Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Free addins Office Menu-2003 for Office-2007)
If You Can't Excel with Talent, Triumph with Effort.
 
Try this macro where you are asked for the NUMBER such as 123
Sub matchemall()
wo = "WO" & InputBox("Enter workorder num")
lr = Cells(Rows.Count, "a").End(xlUp).Row
With Worksheets("sheet5").Range("a1:a" & lr)
Set c = .Find(wo, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
ms = ms & " & " & c.Offset(, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
MsgBox wo & " used " & Right(ms, Len(ms) - 2)
End With
End Sub
 
Say your datalist is on Sheet 2, from A1 to B100.

On Sheet 1, you enter the W/O number to look up in A1,
And the P/Ns for that W/O will display along Row 1, from B1 to whatever.

Enter this *array* formula in B1:

=INDEX(Sheet2!$B$1:$B$100,SMALL(IF(Sheet2!$A$1:$A$100=$A1,ROW($A$1:$A$100),""),COLUMNS($A:A)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE entry, copy across as many columns as you anticipate you'll
need to return all the P/Ns.

When you run out of P/Ns to display, you'll get a #NUM! error, telling you
that *all* available numbers are displayed.

You can copy this formula down and across, if you wish to display numerous
W/O contents at the same time, by entering additional W/O numbers down
Column A.

If you don't wish to see those #NUM! errors, the error trap to eliminate
them will make the formula a little longer:

=
IF(COUNTIF(Sheet2!$A$1:$A$100,$A1)>=COLUMNS($A:A),INDEX(Sheet2!$B$1:$B$100,SMALL(IF(Sheet2!$A$1:$A$100=$A1,ROW($A$1:$A$100),""),COLUMNS($A:A))),"")

This is *also* an array formula, so don't forget the CSE!
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Say, I have a worksheet that has Column A for work orders, Column B
for parts

A work order may contain many parts on different rows:

Column A Column B
WO123 PN5
WO123 PN6
WO123 PN7
WO121 PN9
WO122 PN4
WO122 PN7
etc

I want to automatically "look up" a work order and "import" the parts
used into another worksheet - to create a "flat/horizontal" statement
like "WO122 used PN4 and PN7". How do I do this?
 
Back
Top