We help IT Professionals succeed at work.

Excel: Sort Column Automatically Based on Dynamic Recalculations of Another Column

jfdinneen
jfdinneen asked
on
375 Views
Last Modified: 2012-11-06
I need to automatically sort (hi-lo) a column of integers (no duplicates) in a summary worksheet based on dynamic recalculations of data (using dynamic names - QIDs, RVs) in a separate data worksheet (see below)...

wksht_Summary
QIDs
2
6
3
4
1
5  

wksht_Data
QIDs       RVs
1            (0.92)
2             1.42
4            (0.09)
5            (0.92)
6             0.40
2             1.42
6             0.40
3             0.08
4            (0.09)
6             0.40
4            (0.09)
6             0.40

Please advise?

Best wishes,

John
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Sorting by what?  Sum?  Average?  Ascending or descending?

Author

Commented:
Patrick,

Thanks for the follow-up.

I need the dynamic range - QIDs - in the summary worksheet (wksht_Summary) sorted highest to lowest based on the values of the dynamic range, RVs, which is automatically recalculated as new information is added to the data worksheet (wksht_Data). Ideally, I would prefer a formula solution rather than vba.

Best wishes,

John
CERTIFIED EXPERT
Top Expert 2010

Commented:
You have not completely answered the question, as you have not indicated what kind of aggregation you want to use on the RVs values.

In any event, the attached workbook uses a PivotTable to sort the QID values.  The source for the PivotTable is a dynamic named range, PivotRange, and I use code described in my article on ensuring that your PivotTables automatically update to force the PT to update and stay current when the data change.
Q-27582220.xls

Author

Commented:
Patrick,

Apologies. I thought I had indicated a simple hi-lo ranking.

Unfortunately, I want to avoid a pivot table solution and would prefer a straightforward formula solution or, failing that, a VBA solution if possible.

Best wishes,

John
CERTIFIED EXPERT
Top Expert 2010

Commented:
John,

Sorry, I really am not trying to be difficult, it's just that I am getting frustrated because this is the third time I have asked for it.

You write:

I thought I had indicated a simple hi-lo ranking

Hi-lo based on what, precisely?

Your sample data indicates that, for any given QID, there are potentially more than one record.  Each of those records have a RV value associated with it.

When ranking the QID by RV, your results show a single record for each QID.  Thus, you must want to have some sort of aggregation applied to the RV values for each QID.  What aggregation do you want?  The standard ones are sum, average, minimum, maximum, count, variance, and standard deviation.

And what, exactly, is wrong with a PivotTable, if it gets you where you need to go (that is, a ranked list)?

Author

Commented:
Patrick,

You are correct - I am too close to the problem - sum of RVs is the aggregation I require. As for pivot tables, I am not a fan.

Best wishes,

John
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
EQX

Commented:
correction:

AF9:  =IF(B9>0,TEXT(10^6/MAX(T9,1),"0000000"),V)

and note:  reciprocol eg has 4 levels tested in my formulas..
for:  ABS(AI9-4)+1

AI9:  =IF(B9>0,IF(EP9*IF(BS9>6,1,10)<MAX(EY9,FE9),IF(EP9*IF(BS9>6,1,10)<FE9,3,2),IF(EP9<FF9,1,0)),0)

EP9:  (quantity to buy)
=IF(FP501=0,"",FLOOR($AE$2/IF(DO501>0,DO501,IF(F501=0,FP501,DH501)),$EP$7))

V9:    (volume, has 6 levels)
=IF(EY9="",0,IF(EY9>$AB$3,6,IF(EY9>$AB$4,5,IF(OR(EY9>$AB$5,AI9>$U$1),4,IF(EY9>$AB$6,3,IF(EY9>$AB$7,2,1))))))

6 levels reciprocol eq (as part of original at top):    ABS(AI9-6)+1
 - at top thru:  v9: daily vol,  w9:  3mo avg 6 levels anded with v9

W9:  =IF(FF9=0,0,IF(FF9>$AC$3,6,IF(FF9>$AC$4,5,IF(FF9>$AC$5,4,IF(FF9>$AC$6,3,IF(FF9>$AC$7,2,1))))))

Z9:  V9 & W9  "and'd" together by levels
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.