Ranking with criteria

[macov_vanbasten]

Dear sirs,

I have a data which displays transaction line by line. I would like to rank Top 10 Amount by region. Is there a way I could use formula to automatically rank the data I want and place into a fixed table? Please see attached file.

Currently I need to pivot, then filter "Region", then rank Amount, then copy to the summary table.

Thank you in advance for your help.

-macov

[ramananhrm]

I have solved for 'Central' region. Same logic can be followed for all the regions.

Please try this file.

[macov_vanbasten]

Thank you for your great sharing, 'ramanahrm'.

However, I will need to rank by "Sum of amount", while your formula pick an amount in a cell to rank.

[samba_ravi]

See the attachment

[macov_vanbasten]

Sorry if I have not made myself very clear. Please see expected result attached.

million thanks for your time.

[samba_ravi]

then it is better to use pivot table

[macov_vanbasten]

Is there any way to create an automated for this kind of Sum and ranking? I have to do for 9 sub-region. I try to avoid the repetition of pivot+filter+copy+paste 18 times. (9 sub region x 2 amount)

Greatly appreciated your help, nflsales.

[kvsrinivasamurthy]

Pl see attached file with formulas.
Data is copied to another location and sorted on Region and then ID.Formulas are used in K & L columns.If everything is required automatically it can be done through Macro. For other regions formulas can be extended.

[macov_vanbasten]

Pl see attached file with formulas.
Data is copied to another location and sorted on Region and then ID.Formulas are used in K & L columns.If everything is required automatically it can be done through Macro. For other regions formulas can be extended.

kvsrinivasamurthy,

I don't see your attached file. Please kindly re-post. Thank you.

[kvsrinivasamurthy]

Pl Find the attached file.Data Is copied to other location and sorted on Region and then ID.Formulas are in K & L Columns .
Original data is DELETED to reduce the File Size.

[macov_vanbasten]

Pl Find the attached file.Data Is copied to other location and sorted on Region and then ID.Formulas are in K & L Columns .
Original data is DELETED to reduce the File Size.

It's fantastic. Thank you very much.

[kvsrinivasamurthy]

Thanks for the compliments. One more trial . Pl see.

With Original data also it is possible to get result.(File size is too large to upload).
(To enter Array Formula ,Paste the Formula and press Ctrl+Shift+Enter Keys together)

IN Data Sheet
The Below are Array Formulas
IN E2
=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=COUNTIFS($A$2:$A$19206,$A2,$B$2:$B$19206,$B2),SUMIFS($C$2:$C2,$A$2:$A2,$A2,$B$2:$B2,$B2),"")
IN F2
=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=COUNTIFS($A$2:$A$19206,$A2,$B$2:$B$19206,$B2),SUMIFS($D$2:$D2,$A$2:$A2,$A2,$B$2:$B2,$B2),"")
Drag both till Row 19206

IN "Remaining Amnt" Sheet

In C3
=IF($C3<>"",INDEX(Data!$B$2:$B$19206,MATCH($C3,Data!$E$2:$E$19206,0)),"")
In D3 (Array Formula)
=IFERROR(LARGE(IF(TRIM(Data!$A$2:$A$19206)="Central",Data!$E$2:$E$19206,""),ROW(A1)),"")

In C17
=IF($C17<>"",INDEX(Data!$B$2:$B$19206,MATCH($C17,Data!$E$2:$E$19206,0)),"")
In D17(Array Formula)
=IFERROR(LARGE(IF(TRIM(Data!$A$2:$A$19206)="East",Data!$E$2:$E$19206,""),ROW(A1)),"")

Drag downwards as required

IN "Over Due" Sheet

IN C3
=IF($C3<>"",INDEX(Data!$B$2:$B$19206,MATCH($C3,Data!$E$2:$E$19206,0)),"")
IN D3(ARRAY)
=IFERROR(LARGE(IF(TRIM(Data!$A$2:$A$19206)="Central",Data!$E$2:$E$19206,""),ROW(A1)),"")

IN C17
=IF($C17<>"",INDEX(Data!$B$2:$B$19206,MATCH($C17,Data!$E$2:$E$19206,0)),"")
In D17 (ARRAY)
=IFERROR(LARGE(IF(TRIM(Data!$A$2:$A$19206)="East",Data!$E$2:$E$19206,""),ROW(A1)),"")

Drag downwards as required