Splitting a list into columns

[darkblade80]

Hi there!

Hoping someone can help.

So I have a table > column A has the ID, Column B has the Associate Name and column C has the badge type. What I want is to have 2 separate columns that list the names from the 2 separate badge types. How do I do this without using VBA. I'm trying to figure out the formula, but no luck. I am using Excel 2019. I've been told it can't be done without VBA but there must be right?

 

[Scott T]

Is this what your want? Note you will need to copy down far enough to make sure the results are not truncated.

Book1
	A	B	C	D	E	F	G
1	ID	name	badge			blue	green
2	234	Michael	blue			Michael	Seonad
3	55234	Seonad	green			Craig	Pawel
4	21342	Craig	blue			Kristina	Gabriel
5	4566	Pawel	green
6	345	Kristina	blue
7	657	Gabriel	green
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=IF(ROWS($F$2:F2)>COUNTIF($C$2:$C$7,$F$1),"",INDEX($B$2:$B$7,SMALL(IF($C$2:$C$7=$F$1,ROW($C$2:$C$7)-ROW($C$2)+1),ROWS($F$2:F2))))
G2:G7	G2	=IF(ROWS($F$2:G2)>COUNTIF($C$2:$C$7,$G$1),"",INDEX($B$2:$B$7,SMALL(IF($C$2:$C$7=$G$1,ROW($C$2:$C$7)-ROW($C$2)+1),ROWS($F$2:G2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Welcome to the MrExcel board!

You could also try this formula in F2 and copy across and down.

21 11 27.xlsm
	A	B	C	D	E	F	G
1	ID	name	badge			blue	green
2	234	Michael	blue			Michael	Seonad
3	55234	Seonad	green			Craig	Pawel
4	21342	Craig	blue			Kristina	Gabriel
5	4566	Pawel	green
6	345	Kristina	blue
7	657	Gabriel	green
8
Badges

Cell Formulas
Range	Formula
F2:G7	F2	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$7)/($C$2:$C7=F$1),ROWS(F$2:F2))),"")

 

[darkblade80]

Is this what your want? Note you will need to copy down far enough to make sure the results are not truncated.

Book1
	A	B	C	D	E	F	G
1	ID	name	badge			blue	green
2	234	Michael	blue			Michael	Seonad
3	55234	Seonad	green			Craig	Pawel
4	21342	Craig	blue			Kristina	Gabriel
5	4566	Pawel	green
6	345	Kristina	blue
7	657	Gabriel	green
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=IF(ROWS($F$2:F2)>COUNTIF($C$2:$C$7,$F$1),"",INDEX($B$2:$B$7,SMALL(IF($C$2:$C$7=$F$1,ROW($C$2:$C$7)-ROW($C$2)+1),ROWS($F$2:F2))))
G2:G7	G2	=IF(ROWS($F$2:G2)>COUNTIF($C$2:$C$7,$G$1),"",INDEX($B$2:$B$7,SMALL(IF($C$2:$C$7=$G$1,ROW($C$2:$C$7)-ROW($C$2)+1),ROWS($F$2:G2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

This is amazing! Thank you so much to both you and Peter_SSs. I really appreicate this. I have a lot more ideas That I would love to discuss and will pot in the forum soon!

 

[darkblade80]

Welcome to the MrExcel board!

You could also try this formula in F2 and copy across and down.

21 11 27.xlsm
	A	B	C	D	E	F	G
1	ID	name	badge			blue	green
2	234	Michael	blue			Michael	Seonad
3	55234	Seonad	green			Craig	Pawel
4	21342	Craig	blue			Kristina	Gabriel
5	4566	Pawel	green
6	345	Kristina	blue
7	657	Gabriel	green
8
Badges

Cell Formulas
Range	Formula
F2:G7	F2	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$7)/($C$2:$C7=F$1),ROWS(F$2:F2))),"")

Thank you for this!!!!! I really appreicate this. I have a lot more ideas That I would love to discuss and will put in the forum soon!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.