Find data in columns, then place in rows

  • Thread starter Thread starter couriced
  • Start date Start date
C

couriced

I'm stumped as to how to do this. I've tried several approaches but my
Excel skills aren't quite there I guess.

Here's what I'm trying to do...data is setup in columns Like below:

A B
EMP ID CUST ID
001 AAA
001 BBB
001 CCC
002 DDD
003 EEE
003 FFF
004 GGG

I'm trying to search the data for matches, and have it appear in rows
like this:

A B C D
EMP ID
001 AAA BBB CCC
002 DDD
003 EEE FFF
004 GGG


Any suggestions are greatly appreciated!
 
Assumptions:

A1:B8 contains your source data

First row contains your headers/labels

Column D, starting at D2, contains your list of unique 'Employee ID'
numbers

Formula:

E2, copied across and down:

=IF(COLUMNS($E2:E2)<=COUNTIF($A$2:$A$8,$D2),INDEX($B$2:$B$8,SMALL(IF($A$2
:$A$8=$D2,ROW($B$2:$B$8)-ROW($B$2)+1),COLUMNS($E2:E2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Note that you can generate a unique list of 'Employee IDs' by using
Advanced Filter and checking 'Unique records only'.

Hope this helps!
 
Back
Top