match or lookup?

[Dave]

I have problem in getting the final table which look like the following:

col A B C D E ...
row1 120 PC Keyboard
row2 310 Monitor
row3 450 Mouse Scanner Server CDROM

Our customers normally will give us very long list which look something like
this:
120 PC
310 monitor
120 Keyboard
450 Mouse
450 Scanner
450 Server
450 CDROM

We can do it by using the filter function and copy the item one by one, but
sometimes we did left out some of it. Do anyone have any idea in doing that?

 

[Biff]

Hi!

Do you already have the unique ID's separated out into
another list?

I'll assume you do and they're on Sheet2 in col A. I'll
also assume the unsorted list is on Sheet1 starting in
cell A1.

On Sheet2 cell B1 enter this array formula using the key
combo of CTRL,SHIFT,ENTER:

=INDEX(Sheet1!$B$1:$B$7,SMALL(IF(Sheet1!$A$1:$A$7=$A1,ROW
($A$1:$A$7)),COLUMN(A:A)))

Copy this down as needed and then across until you get a
full column of #NUM! errors meaning there are no more
matching values.

To get rid of the #NUM! errors with the formula range
still selected hit F5 click the Special button then click
Formulas. Uncheck all the options EXCEPT Errors then click
OK. All the cells that have #NUM! errors are now selected.
Point to the range and right click then select Clear
Contents.

Biff