Single Formula

[ExcelNewbie2020]

Using single formula how can we transform the source data to expected output..
i need to put the amount to its respective size based on column header. thank you

Book1
	A	B	C	D	E	F	G	H	I	J
1	SOURCE						EXPECTED OUTPUT
2	Name	Amount	Size				Name	Small	Medium	Large
3	Aariz Gentry	28.89	Small				Aariz Gentry	28.89
4	Wilf Klein	4.05	Medium				Wilf Klein		4.05
5	Vivian Dolan	55.09	Small				Vivian Dolan	55.09
6	Manisha Wilkinson	47.73	Small				Manisha Wilkinson	47.73
7	Ezmae Pugh	11.92	Small				Ezmae Pugh	11.92
8	Ira Savage	3.24	Small				Ira Savage	3.24
9	Tayyib Cervantes	92.80	Small				Tayyib Cervantes	92.80
10	Luke Fitzpatrick	46.80	Small				Luke Fitzpatrick	46.80
11	Cienna Hewitt	83.79	Large				Cienna Hewitt			83.79
12	Mila Davison	60.10	Small				Mila Davison	60.10
13	Cassius Carty	49.45	Medium				Cassius Carty		49.45
14	Prisha Povey	7.33	Small				Prisha Povey	7.33
15	Amaya Mccall	2.64	Small				Amaya Mccall	2.64
16	Adaline Browne	35.12	Small				Adaline Browne	35.12
17	Eshal Oliver	9.44	Small				Eshal Oliver	9.44
Sheet7

 

[Cubist]

Try:

=PIVOTBY(A3:A17,C3:C17,B3:B17,SINGLE,,0,,0)

 

[HongRu]

Try.

=HSTACK(A3:A17,IF(C3:C17={"Small","Medium","Large"},B3:B17,""))

 

[ExcelNewbie2020]

Try:

=PIVOTBY(A3:A17,C3:C17,B3:B17,SINGLE,,0,,0)

it worked like a charm.. however, i noticed that there are duplicate values from my actual data under column A.
Would it be possible for pivotby to show all values in the row fields (not only unique values)? thank you very much

 

[Cubist]

noticed that there are duplicate values from my actual data under column A.

Duplicate how? If they're duplicate in A but differs in size (col C), it should still work.

Book1
	A	B	C	D	E	F	G	H	I	J
1	SOURCE
2	Name	Amount	Size					Small	Medium	Large
3	Aariz Gentry	28.89	Small				Aariz Gentry	28.89	4.05
4	Aariz Gentry	4.05	Medium				Adaline Browne	35.12
5	Vivian Dolan	55.09	Small				Amaya Mccall	2.64
6	Manisha Wilkinson	47.73	Small				Cassius Carty		49.45
7	Ezmae Pugh	11.92	Small				Cienna Hewitt			83.79
8	Ira Savage	3.24	Small				Eshal Oliver	9.44
9	Tayyib Cervantes	92.8	Small				Ezmae Pugh	11.92
10	Luke Fitzpatrick	46.8	Small				Ira Savage	3.24
11	Cienna Hewitt	83.79	Large				Luke Fitzpatrick	46.8
12	Mila Davison	60.1	Small				Manisha Wilkinson	47.73
13	Cassius Carty	49.45	Medium				Mila Davison	60.1
14	Prisha Povey	7.33	Small				Prisha Povey	7.33
15	Amaya Mccall	2.64	Small				Tayyib Cervantes	92.8
16	Adaline Browne	35.12	Small				Vivian Dolan	55.09
17	Eshal Oliver	9.44	Small
Sheet4

Cell Formulas
Range	Formula
G2:J16	G2	=PIVOTBY(A3:A17,C3:C17,B3:B17,SINGLE,,0,,0,-1)
Dynamic array formulas.

 

[ExcelNewbie2020]

Duplicate how? If they're duplicate in A but differs in size (col C), it should still work.

Book1
	A	B	C	D	E	F	G	H	I	J
1	SOURCE
2	Name	Amount	Size					Small	Medium	Large
3	Aariz Gentry	28.89	Small				Aariz Gentry	28.89	4.05
4	Aariz Gentry	4.05	Medium				Adaline Browne	35.12
5	Vivian Dolan	55.09	Small				Amaya Mccall	2.64
6	Manisha Wilkinson	47.73	Small				Cassius Carty		49.45
7	Ezmae Pugh	11.92	Small				Cienna Hewitt			83.79
8	Ira Savage	3.24	Small				Eshal Oliver	9.44
9	Tayyib Cervantes	92.8	Small				Ezmae Pugh	11.92
10	Luke Fitzpatrick	46.8	Small				Ira Savage	3.24
11	Cienna Hewitt	83.79	Large				Luke Fitzpatrick	46.8
12	Mila Davison	60.1	Small				Manisha Wilkinson	47.73
13	Cassius Carty	49.45	Medium				Mila Davison	60.1
14	Prisha Povey	7.33	Small				Prisha Povey	7.33
15	Amaya Mccall	2.64	Small				Tayyib Cervantes	92.8
16	Adaline Browne	35.12	Small				Vivian Dolan	55.09
17	Eshal Oliver	9.44	Small
Sheet4

Cell Formulas
Range	Formula
G2:J16	G2	=PIVOTBY(A3:A17,C3:C17,B3:B17,SINGLE,,0,,0,-1)
Dynamic array formulas.

thank you..duplicate in column A and with the same size in column C

 

[Cubist]

PIVOTBY is an aggregate function so it removes duplicates. Try the suggestion in post #3.