Sort list into separate columns

[Guentjo]

I have a need to recreate a databse output in excel. Simply, I have a list of Names, each name can be assigned to a specific group number (1 through 3). I then have 3 columns, one for each group that will contain the appropriate list. The group assignments change frequently, so I'd like to automate the process. Is there a formula or macro that will do this easily. I have attached an example of how it could look (i've hard coded the names in columns D,E,&F). I have a feeling there is a basic solution to this that I just don't know (don't say "use Access")

Book1
	A	B	C	D	E	F
2	Group	Name		Group1	Group2	Group3
3	1	Susan		Chris	Emily	John
4	1	Steve		Steve	Mark	Sara
5	3	Sara		Susan	Christy	Irwin
6	2	Mark
7	3	John
8	3	Irwin
9	2	Emily
10	2	Christy
11	1	Chris
Sheet1

 

[Krishnakumar]

Hi Guentjo,

Book2
	A	B	C	D	E	F	G
1	Group	Name		Group	1	2	3
2	1	Susan			Susan	Mark	Sara
3	1	Steve			Steve	Emily	John
4	3	Sara			Chris	Christy	Irwin
5	2	Mark
6	3	John
7	3	Irwin
8	2	Emily
9	2	Christy
10	1	Chris
Sheet1

It's an array formula. Confirm with Ctrl+Shift+Enter instead of Enter

HTH

 

[Guentjo]

I like it. It's genius!

Thanks. My own step 2 is learning why it works.

 

[Guentjo]

I'm having a problem with the array. It only works if the array starts in row 2. If I insert a row above, or cut and paste the array to a new location, the formula fails. The reference all appear to still be valid, but the output is wrong.

What am I missing?

 

[Krishnakumar]

Hi,

=IF(ROW()-ROW(E$1)>COUNTIF($A$2:$A$10,E$1),"",INDEX($B$1:$B$10,SMALL(IF($A$2:$A$10=E$1,ROW($B$2:$B$10)),ROW()-ROW(E$1))))

Change the bold part with ROW($A2).

HTH