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

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

Hi,

Assume that the data is in range B3:C9. Enter 1,2,3 in B12:D12. In cell
B13, array enter (Ctrl+Shift+Enter) the following formula. Then copy this
formula down and to the right

=IF(ISERROR(INDEX($B$3:$C$9,SMALL(IF($C$3:$C$9=B$1 2,ROW($B$3:$B$9)),ROW(1:1))-ROW($C$2),1)),"",INDEX($B$3:$C$9,SMALL(IF($C$3:$C$ 9=B$12,ROW($B$3:$B$9)),ROW(1:1))-ROW($C$2),1))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"LiAD" wrote in message
...
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

Hi,

Thanks, works perfect.

How would I adapt this formula to read through a list and eliminate the
empty cells? I have just found something else that could benefit from this
type of formula.

Example of inputs

Bill
(blank)
Fred
(blank)
Wilma
Jill
Ben

Output is a list
Bill
Fred
Wilma
Jill
Ben

"Jacob Skaria" wrote:

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