Transpose certain cells based on a condition

[NicoleN]

Hi There,

Hoping someone may have a suggestion for me (VBA, or formula, or other). I want to transpose certain cells based on a condition in a separate column (but same row). To illustrate with an example, I would like all values from column C to be transposed into the relevant row depending on Column B's value. E.g. Column B to contain one line of "Jonathon" and all items in Column C against Jonathon's name are transposed in Row 1. Then Same for other names in Column B. See example of initial data below (A-C columns), and then desired format (E-L columns).
I've seen the INDEX formula used to transpose a certain number of values, but in my case the number of values to be transposed will range from 3 to about 8, so I can set a certain amount in the formula.
Would appreciate any ideas. Thank you in advance!

A B C D E F G H I J K L
[TABLE="width: 788"]
<colgroup><col><col><col span="10"></colgroup><tbody>[TR]
[TD]ABC[/TD]
[TD]Jonathon[/TD]
[TD]Cat[/TD]
[TD] [/TD]
[TD]Jonathon[/TD]
[TD]Cat[/TD]
[TD]Dog[/TD]
[TD]Bird[/TD]
[TD]Fish[/TD]
[TD]Elephant[/TD]
[TD]Panda[/TD]
[TD]Bear[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Jonathon[/TD]
[TD]Dog[/TD]
[TD] [/TD]
[TD]Peter[/TD]
[TD]Dog[/TD]
[TD]Bird[/TD]
[TD]Fish[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DUA[/TD]
[TD]Jonathon[/TD]
[TD]Bird[/TD]
[TD] [/TD]
[TD]Jessica[/TD]
[TD]Elephant[/TD]
[TD]Panda[/TD]
[TD]Dog[/TD]
[TD]Bird[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WQH[/TD]
[TD]Jonathon[/TD]
[TD]Fish[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]FDI[/TD]
[TD]Jonathon[/TD]
[TD]Elephant[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]PDU[/TD]
[TD]Jonathon[/TD]
[TD]Panda[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IDJ[/TD]
[TD]Jonathon[/TD]
[TD]Bear[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JUR[/TD]
[TD]Peter[/TD]
[TD]Dog[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]FOR[/TD]
[TD]Peter[/TD]
[TD]Bird[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DEG[/TD]
[TD]Peter[/TD]
[TD]Fish[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IMB[/TD]
[TD]Jessica[/TD]
[TD]Elephant[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]PED[/TD]
[TD]Jessica[/TD]
[TD]Panda[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IRF[/TD]
[TD]Jessica[/TD]
[TD]Dog[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IND[/TD]
[TD]Jessica[/TD]
[TD]Bird[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ABC	Jonathon	Cat		Jonathon	Cat	Dog	Bird	Fish	Elephant	Panda	Bear
2	XYZ	Jonathon	Dog		Peter	Dog	Bird	Fish
3	DUA	Jonathon	Bird		Jessica	Elephant	Panda	Dog	Bird
4	WQH	Jonathon	Fish
5	FDI	Jonathon	Elephant
6	PDU	Jonathon	Panda
7	IDJ	Jonathon	Bear
8	JUR	Peter	Dog
9	FOR	Peter	Bird
10	DEG	Peter	Fish
11	IMB	Jessica	Elephant
12	PED	Jessica	Panda
13	IRF	Jessica	Dog
14	IND	Jessica	Bird
Sheet1

If E1:E3 must also produced by means of a formula...

In E1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$14,SMALL(IF(FREQUENCY(IF(1-($B$1:$B$14=""),MATCH($B$1:$B$14,$B$1:$B$14,0)),
     ROW($B$1:$B$14)-ROW($B$1)+1),ROW($B$1:$B$14)-ROW($B$1)+1),ROWS($E$17:E17))),"")

In F1 control+shift+enter, not just enter, copy across, and down:

=IFERROR(IF($E17="","",INDEX($C$1:$C$14,SMALL(IF($B$1:$B$14=$E17,ROW($C$1:$C$14)-ROW($C$1)+1),COLUMNS($F1:F1)))),"")

 

[NicoleN]

Thank you so much Aladin, very helpful! Some new functions I've learnt

 

[Aladin Akyurek]

Thank you so much Aladin, very helpful! Some new functions I've learnt

You are very welcome.