Indexing from largest to smallest

[Dougmclain]

[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500, align: right"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Interest[/TD]
[TD]Clubhouse[/TD]
[TD]Dividend[/TD]
[TD]Rental[/TD]
[TD]Parking[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[TD]100[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Clubhouse[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Dividend[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Interest[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Parking[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]130[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to create table 2 in descending order based on the row data in table 1. I only want a row in table 2 with a description to show up if the number is greater than zero and then total the numbers. I used =large(A2:E2,1) for cell B4, and =large(A2:E2,2) for cell B5. I can't figure out how to get the description cell above to show up in Table 2 in the correct order. I also don't know how to exclude a row if the value of a cell is zero, and then to put the total in the correct cell.

Thanks,

 

[Latchmaker]

Copy Rows 1 and 2
Paste Special Transpose in Chart 2 Location
Sort Largest to smallest on 2nd Column
Remove all with zero or less then zero Qty
Total 2nd Column

 

[FDibbins]

This will do what you want, but how do you want to handle duplicated?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

6​

[/td][td]Clubhouse[/td][td]

100​

[/td][/tr]

[tr][td]

7​

[/td][td]Dividend[/td][td]

20​

[/td][/tr]

[tr][td]

8​

[/td][td]Interest[/td][td]

5​

[/td][/tr]

[tr][td]

9​

[/td][td]Interest[/td][td]

5​

[/td][/tr]
[/table]
A6=INDEX($A$2:$E$2,MATCH(B6,$A$3:$E$3,0))
B6=LARGE($A$3:$E$3,ROWS($A$1:A1))
both copied down

 

[Dougmclain]

Thanks, that gets me column A in the order I need. I want duplicates to show up but, only if they are greater than zero. I want to exclude any number that is zero and then have it total, for example I want Interest and Parking to both show up, one below the other, but I don't want rental to show up because it has a zero.

This will do what you want, but how do you want to handle duplicated?
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Clubhouse[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Dividend[/TD]
[TD]

20​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Interest[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Interest[/TD]
[TD]

5​

[/TD]
[/TR]
</tbody>[/TABLE]

A6=INDEX($A$2:$E$2,MATCH(B6,$A$3:$E$3,0))
B6=LARGE($A$3:$E$3,ROWS($A$1:A1))
both copied down

 

[FDibbins]

In order to ID the duplicates, and so that the INDEX/MATCH can find them, you would need a helper column to give each duplicate it's own unique ID. Is that something you would be OK with?

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td] Interest[/td][td] Clubhouse[/td][td] Dividend[/td][td] ISP[/td][td] Rental[/td][td] Parking[/td][/tr]


[tr][td]

2​

[/td][td] 5[/td][td] 100[/td][td] 20[/td][td] 20[/td][td] 0[/td][td] 5[/td][/tr]


[tr][td]

3​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

4​

[/td][td] 5[/td][td][/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

5​

[/td][td][/td][td][/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

6​

[/td][td] Clubhouse[/td][td] 100[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

7​

[/td][td] Dividend[/td][td] 20[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

8​

[/td][td] ISP[/td][td] 20[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td] Interest[/td][td] 5[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

10​

[/td][td] Parking[/td][td] 5[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

11​

[/td][td] [/td][td] [/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In A4 just enter:

=COUNTIFS(A2:F2,">0")

In A6 control+shift+enter, not just enter, and copy down:

=IF($B6="","",INDEX($A$1:$F$1,SMALL(IF(ISNUMBER(1/$A$2:$F$2),
    IF($A$2:$F$2=$B6,COLUMN($A$1:$F$1)-COLUMN($A$1)+1)),
    COUNTIFS($B$6:B6,B6))))

In B6 control+shiift+enter and copy down:

=IF(ROWS($B$6:B6)<=$A$4,LARGE(IF(ISNUMBER(1/$A$2:$F$2),$A$2:$F$2),
    ROWS($B$6:B6)),"")

 

[Dougmclain]

Thank you Aladin, that worked great. I like how you used the =count(a2:f2>0) that really simplified it. How would I get the total to show up in B11, or another cell in B depending on how many rows in the result?

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] Interest[/TD]
[TD] Clubhouse[/TD]
[TD] Dividend[/TD]
[TD] ISP[/TD]
[TD] Rental[/TD]
[TD] Parking[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD] 5[/TD]
[TD] 100[/TD]
[TD] 20[/TD]
[TD] 20[/TD]
[TD] 0[/TD]
[TD] 5[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD] 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD] Clubhouse[/TD]
[TD] 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD] Dividend[/TD]
[TD] 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD] ISP[/TD]
[TD] 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD] Interest[/TD]
[TD] 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD] Parking[/TD]
[TD] 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In A4 just enter:

=COUNTIFS(A2:F2,">0")

In A6 control+shift+enter, not just enter, and copy down:

=IF($B6="","",INDEX($A$1:$F$1,SMALL(IF(ISNUMBER(1/$A$2:$F$2),
    IF($A$2:$F$2=$B6,COLUMN($A$1:$F$1)-COLUMN($A$1)+1)),
    COUNTIFS($B$6:B6,B6))))

In B6 control+shiift+enter and copy down:

=IF(ROWS($B$6:B6)<=$A$4,LARGE(IF(ISNUMBER(1/$A$2:$F$2),$A$2:$F$2),
    ROWS($B$6:B6)),"")

 

[Dougmclain]

Yes, I liked the way he Aladin used the count cell, to make the formula work. I just need to figure out a way to make the total, to show up in the cell below, given how many rows there are.

Thanks for your help

 

[Aladin Akyurek]

Yes, I liked the way he Aladin used the count cell, to make the formula work. I just need to figure out a way to make the total, to show up in the cell below, given how many rows there are.

Thanks for your help

How about TOTAL: in A5 and the required sum in B5, where B5 houses the following formula?

=SUM(OFFSET(B6,0,0,A4))

Good enough?

 

[Dougmclain]

How about TOTAL: in A5 and the required sum in B5, where B5 houses the following formula?

=SUM(OFFSET(B6,0,0,A4))

Good enough?

I have to have the total be below the last number, I can have the total show up in A5 but I still need to have it show up on the line below the last number such as in B11.

Thanks,