TransWikia.com

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

Super User Asked on December 11, 2021

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

2 Answers

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")))

enter image description here

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)

Answered by JvdV on December 11, 2021

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.

enter image description here

Answered by PeterH on December 11, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP