Correction/Help with formula

[MrsFrankieH]

Hello All,

My computer is a Dell and my operating system is Windows 10.

Can you please help me with a formula to sort numbers in descending order?

In the cells (AQ1:AU6), I used the Countif function to count the numbers. The top row (A1:AO1) are the numbers that were counted and the second row (A2:AO2) are how totals of each number.

The second row is where I need the numbers listed in descending order.



When I wrote the formula to do this on my spreadsheet, the Countif rows and descending rows were on the same row. I had to stack them to fit here.

This are the formulas I used…

=LARGE($A2:$AO2,COLUMNS(AQ2:AQ2))
=SMALL(IF($A2:$AO2=AQ2,$A1:$AO1),COUNTIF($AQ2:AQ2, AQ2)) Ctrl+Shift+Enter.

When I dragged the numbers across, it would only go as far as two cells and then the rest were errors.

Can you help please?

Thanks in advance!!

 

[jsuarezg]

Hello,

Use this formula

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #0057d6}span.s1 {color: #000000}span.s2 {color: #006107}</style>=LARGE($A$2:$AO$2,A1)

The LARGE function selects from the array (A2:AO2) the A1 largest number. If you want the first largest you put the A1, the second largest is the A2 that have number 2 an so on.

Juan David

 

[MrsFrankieH]

Hello,

Use this formula

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #0057d6}span.s1 {color: #000000}span.s2 {color: #006107}</style>=LARGE($A$2:$AO$2,A1)

The LARGE function selects from the array (A2:AO2) the A1 largest number. If you want the first largest you put the A1, the second largest is the A2 that have number 2 an so on.

Juan David

Hello Juan,

Thank you for the quick reply!! It does work for the quantity (A5:AO5), but not for the numbers at the top (A4:AO4).

Thank you for your help because it's a start.

 

[inactiveuserps07]

Place into cell A4 (use Ctrl-Shift-Enter):

=IF(COLUMN()=1,INDEX($A1:$AO1,MATCH(A5,$A2:$AO2,0)),INDEX($A1:$AO1,1,SMALL(IF($A2:$AO2=A5,COLUMN($A2:$AO2),""),COUNTIF(INDIRECT("$A4:"&ADDRESS(4,COLUMN())),A5))))

Place into cell A5:

=LARGE($A2:$AO2,COLUMN())

Select A4 and A5 together. Click-and-hold the tiny black box in the lower right corner of the heavy border, then drag across to Column AO. Both formulas will be copied across.

 

[MrsFrankieH]

Place into cell A4 (use Ctrl-Shift-Enter):

=IF(COLUMN()=1,INDEX($A1:$AO1,MATCH(A5,$A2:$AO2,0)),INDEX($A1:$AO1,1,SMALL(IF($A2:$AO2=A5,COLUMN($A2:$AO2),""),COUNTIF(INDIRECT("$A4:"&ADDRESS(4,COLUMN())),A5))))

Place into cell A5:

=LARGE($A2:$AO2,COLUMN())

Select A4 and A5 together. Click-and-hold the tiny black box in the lower right corner of the heavy border, then drag across to Column AO. Both formulas will be copied across.

Hello Erik!! Thank you for the speedy reply. I placed the formulas and got the message "Microsoft Excel cannot calculate a formula..."

 

[inactiveuserps07]

Sorry about that. I originally had my calculations in Rows 3 and 4, and then moved them when I noticed yours were in 4 and 5. Forgot to change one part of the formula. This one should work for A4:

=IF(COLUMN()=1,INDEX($A1:$AO1,MATCH(A5,$A2:$AO2,0)),INDEX($A1:$AO1,1,SMALL(IF($A2:$AO2=A5,COLUMN($A2:$AO2),""),COUNTIF(INDIRECT("$A5:"&ADDRESS(5,COLUMN())),A5))))

... or if you think you'll ever move these calculation rows up or down, you can use this version:

=IF(COLUMN()=1,INDEX($A1:$AO1,MATCH(A5,$A2:$AO2,0)),INDEX($A1:$AO1,1,SMALL(IF($A2:$AO2=A5,COLUMN($A2:$AO2),""),COUNTIF(INDIRECT("$A"&ROW()+1&":"&ADDRESS(ROW()+1,COLUMN())),A5))))

Remember to Ctrl-Shift-Enter it. Then just drag it across.

Row 5 should have been fine.

 

[MrsFrankieH]

Sorry about that. I originally had my calculations in Rows 3 and 4, and then moved them when I noticed yours were in 4 and 5. Forgot to change one part of the formula. This one should work for A4:

=IF(COLUMN()=1,INDEX($A1:$AO1,MATCH(A5,$A2:$AO2,0)),INDEX($A1:$AO1,1,SMALL(IF($A2:$AO2=A5,COLUMN($A2:$AO2),""),COUNTIF(INDIRECT("$A5:"&ADDRESS(5,COLUMN())),A5))))

... or if you think you'll ever move these calculation rows up or down, you can use this version:

=IF(COLUMN()=1,INDEX($A1:$AO1,MATCH(A5,$A2:$AO2,0)),INDEX($A1:$AO1,1,SMALL(IF($A2:$AO2=A5,COLUMN($A2:$AO2),""),COUNTIF(INDIRECT("$A"&ROW()+1&":"&ADDRESS(ROW()+1,COLUMN())),A5))))

Remember to Ctrl-Shift-Enter it. Then just drag it across.

Row 5 should have been fine.

Hello Eric!! Thanks again for your formula. I really appreciate it!! Unfortunately, it didn't work as it gave me the wrong value in A4 and when I dragged it across, I got an error message. I did Ctrl Shift Enter.

 

[inactiveuserps07]

Very hard to troubleshoot it from afar. It works when I do it here. ???

And you tried both formulas above, this one first?

=IF(COLUMN()=1,INDEX($A1:$AO1,MATCH(A5,$A2:$AO2,0)),INDEX($A1:$AO1,1,SMALL(IF($A2:$AO2=A5,COLUMN($A2:$AO2),""),COUNTIF(INDIRECT("$A5:"&ADDRESS(5,COLUMN())),A5))))

Are you copying/pasting? Or are you trying to type these in manually? One error can junk it (like a missing $).

 

[MrsFrankieH]

Very hard to troubleshoot it from afar. It works when I do it here. ???

And you tried both formulas above, this one first?

=IF(COLUMN()=1,INDEX($A1:$AO1,MATCH(A5,$A2:$AO2,0)),INDEX($A1:$AO1,1,SMALL(IF($A2:$AO2=A5,COLUMN($A2:$AO2),""),COUNTIF(INDIRECT("$A5:"&ADDRESS(5,COLUMN())),A5))))

Are you copying/pasting? Or are you trying to type these in manually? One error can junk it (like a missing $).

Hi Eric!!

Thank you so much!! I tried both formulas, the one above and the one below. I also copied and pasted the formulas. Maybe I have bad cells?

 

[inactiveuserps07]

Hi MrsFrankieH. I know MrExcel frowns upon attached files; but the goal here is to help. And I can't help any further unless I can see the file. Could you upload it via DropBox, Google Drive or some other means and provide a link? I'll take a look quick. Then, for the benefit of this board and others in the future, I'll explain what I saw as the problem.