Can vlookup be used to retrieve multiple matches
 

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?

Can vlookup be used to retrieve multiple matches
 

Try to use Autofilter.
DataFilterAdvance FilterCheck 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.

wrote in message
oups.com...
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?

Can vlookup be used to retrieve multiple matches
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
oups.com...
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?

Can vlookup be used to retrieve multiple matches
 

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$1 00=$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!
================================================== ===



wrote in message
oups.com...
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?

Can vlookup be used to retrieve multiple matches
 

Hello!

Named Ranges:

Code: A1:A......
Result: B1:B...

Select D1:D10
=IF(ISNUMBER(SMALL(IF(code="WO122",MATCH(Result,Re sult,
0)),ROW(INDIRECT("1:"&ROWS(Result))))),
INDEX(Result,SMALL(IF(code="WO122",MATCH(Result,Re sult,
0)),ROW(INDIRECT("1:""&ROWS(Result))))),"")
Valid with Shift+Ctrl+enter

http://boisgontierjacques.free.fr/fi...nterroCode.xls

JB
http://boisgontierjacques.free.fr/

On 11 août, 15:19, wrote:
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?