Call Column for the data from the same group

[jerung3000]

Hi all..

I need to call Column for the data that from the same group in that column.

Fruits Group.xlsx
	A	B	C	D	E	F	G	H
1							Fruit	Group
2		GroupA	GroupB	GroupC	GroupD		Banana	GroupA
3		Banana	Orange	Grape	BlueBerry		Lychee	GroupA , GroupD
4		Lychee	Apple	Orange	Soursop		Strawbery	GroupA
5		Pomegranate	Pomegranate	Kiwi	Pomegranate		Orange	GroupB , GroupC
6		Strawbery	Mango	Avocado	Lychee		Apple	GroupB
7							Pomegranate	GroupA , GroupB , GroupD
8							Mango	GroupB
9							Grape	GroupC
10							Kiwi	GroupC
11							Avocado	GroupC
12							BlueBerry	GroupD
13							Soursop	GroupD
Sheet1

Cell Formulas
Range	Formula
H2	H2	=INDEX($B$2:$E$2,SUMPRODUCT(MAX(($B$3:$E$6=G2)*(COLUMN($B$3:$E$6))))-COLUMN($B$2)+1)

Already tried with this formula

=INDEX($B$2:$E$2,SUMPRODUCT(MAX(($B$3:$E$6=G2)*(COLUMN($B$3:$E$6))))-COLUMN($B$2)+1)

but this formula only call one group name only.

Hopefully someone can help me to resolve this..

Thank you..

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1							Fruit	Group
2		GroupA	GroupB	GroupC	GroupD		Banana	GroupA
3		Banana	Orange	Grape	BlueBerry		Lychee	GroupA, GroupD
4		Lychee	Apple	Orange	Soursop		Strawbery	GroupA
5		Pomegranate	Pomegranate	Kiwi	Pomegranate		Orange	GroupB, GroupC
6		Strawbery	Mango	Avocado	Lychee		Apple	GroupB
7							Pomegranate	GroupA, GroupB, GroupD
8							Mango	GroupB
9							Grape	GroupC
10							Kiwi	GroupC
11							Avocado	GroupC
12							BlueBerry	GroupD
13							Soursop	GroupD
Master

Cell Formulas
Range	Formula
H2:H13	H2	=TEXTJOIN(", ",,IF($B$3:$E$6=G2,$B$2:$E$2,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jerung3000]

Thanks for your help Fluff.

I already tried the formula that you gave in the forum..But I didn't get the correct value like you do..

Fruits Group.xlsx
	A	B	C	D	E	F	G	H
1							Fruit	Group
2		GroupA	GroupB	GroupC	GroupD		Banana	#VALUE!
3		Banana	Orange	Grape	BlueBerry		Lychee	#VALUE!
4		Lychee	Apple	Orange	Soursop		Strawbery	#VALUE!
5		Pomegranate	Pomegranate	Kiwi	Pomegranate		Orange	#VALUE!
6		Strawbery	Mango	Avocado	Lychee		Apple	#VALUE!
7							Pomegranate	#VALUE!
8							Mango	#VALUE!
9							Grape	#VALUE!
10							Kiwi	#VALUE!
11							Avocado	#VALUE!
12							BlueBerry	#VALUE!
13							Soursop	#VALUE!
Sheet1

Cell Formulas
Range	Formula
H2:H13	H2	=TEXTJOIN(", ",,IF($B$3:$E$6=G2,$B$2:$E$2,""))

 

[Fluff]

It needs to be confirmed with Ctrl Shift Enter & not just Enter.

 

[jerung3000]

It needs to be confirmed with Ctrl Shift Enter & not just Enter.

Ok..thanks for your explanation..Works great..

 

[Fluff]

You're welcome & thanks for the feedback.