Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

In E1
=IF(ROW()COUNTIF($B$1:$B$1000,1),"",INDEX($A$1:$A $1000,SMALL(IF($B$1:$B$1000=1,ROW($A$1:$A$1000)),R OW())))
(and copy down as required)

In F1
=IF(ROW()COUNTIF($B$1:$B$1000,2),"",INDEX($A$1:$A $1000,SMALL(IF($B$1:$B$1000=2,ROW($A$1:$A$1000)),R OW())))
(and copy down as required)

In F1
=IF(ROW()COUNTIF($B$1:$B$1000,3),"",INDEX($A$1:$A $1000,SMALL(IF($B$1:$B$1000=3,ROW($A$1:$A$1000)),R OW())))
(and copy down as required)

and so on...

If this post helps click Yes
---------------
Jacob Skaria


"LiAD" wrote:

Hi,

If I have a list of names in col A and a list of user groups in col B is
there any function in excel i can use to generate a separate list for each
user group?

I would like excel to pick any of the names with say a 1 against it and
create a new list in say col E for user group 1, col F for group 2 etc etc.
The order the names appear is the same as the original list. If someone
changes user group the list updates automatically.

Is this possible using formulas? (I do not want to use macros or filters)

Example of an input list

Fred 1
Bill 2
Wilma 4
Dave 1
Mike 3
Jim 2
John 1

Desired output

1 2 3
Fred Bill Mike
Dave Jim
John