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?