Excel: How to extract repeated values from randomly ordered list?

Question

Say there are 3 columns: The first column indicates names, the second indicates some string variables including height and weight in random order and the third indicates corresponding numeric values.
I am interested in the weight/height ratio of each person. Is there any smarter way to compute all the ratios instead of calculating them one by one? Please see the image below.
 A    B      C   
 Mike height 170  
 Mike weight 40
 Mike income 1000
 Mike sex    0
 Anna income 1500
 Anna weight 60
 Anna height 170
 Carl sex    1
 Carl weight 60
 Carl height 150
 Jo   height 190
 Jo   weight 47
 Jo   sex    1

From the 3 columns above, I want to compute the ratio of weight to height for each person as follows:
 D    E
 Mike 0.235294118 
 Anna 0.352941176
 Carl 0.4
 Jo   0.247368421

JvdV · Answer

If you have Excel O365, this can be done with a single formula in a single cell due to Excel's spilling behaviour. It's purely for educational purposes since more simple formulas are possible. What I tried in E1:
=TRANSPOSE(CHOOSE({1,2},TRANSPOSE(UNIQUE(A1:A13)),SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"weight")/SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"height")))

If you then want to SORT by person's name (or weight/height ratio) you can do so by:
Name:
=SORT(TRANSPOSE(CHOOSE({1,2},TRANSPOSE(UNIQUE(A1:A13)),SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"weight")/SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"height"))),1,1)

Ratio:
=SORT(TRANSPOSE(CHOOSE({1,2},TRANSPOSE(UNIQUE(A1:A13)),SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"weight")/SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"height"))),2,1)

PeterH · Answer

In T1 you can use the below formula, then drag down for the other names.
=SUMIFS(Q:Q,O:O,S1,P:P,"HEIGHT")/SUMIFS(Q:Q,O:O,S1,P:P,"WEIGHT")

Ahh this answer was to go with the original image.
You may need to adjust the columns to fit if you have moved the columns as per the update to the question.

Excel: How to extract repeated values from randomly ordered list?

2 Answers

Add your own answers!

Ask a Question