Variable Ranking basis Transaction Date

[Venkat76]

Dear Friends - I am trying to place a rank against actual transaction data basis its date of execution - referred against a rate master. I have attached the sample example details - where the desired rank is needed in yellow colored column (including duplicate ranks with continuity as well).

For eg. as on 5th Aug, transactions were done with Vendor A and B, both had rate of 100 (index/match done from rate master basis date) - so the ranking of 1 basis all "valid rates in rate master as on 5th Aug".

My original database runs into approx.50K rows.

Please assist.

Thanks
Venkat

 

[srinidhi]

Hi Venkat,

How do you arrive at the rank.
If rate is 100-200 the rank is 1, from 201-300 the rank is 2. Please do let me know if my understanding is correct.

 

[Venkat76]

Hi Srinidhi - as for normal ranking with just valid rates, I sort the table (if possible) for rates in ascending order and use a simple IF function; if table not possible to sort, then I use the SUM(IF(FREQUENCY nesting, help which I took from this forum earlier - which served the purpose.

This database is new wherein Ranking needs to be identified basis 'Transaction Date'.

For eg - on 5th Aug - there were two valid rates in Rate Master Table - Vendor A as 100 and Vendor B as 100. So both got Rank 1.

On 16th Sep - Rate Master has again 2 valid rates - Vendor A is 200 and Vendor B is 250. So 250 got Rank 2 within this combination in Actual Transaction Data.

On 20th Sep - Rate Master has 2 valid rates - and Vendor A with 200 got Rank 1 in the Actual Transaction Data.

Therefore, the critical element here is, the group of rates valid as on a particular date in the Rate Master needs to be identified in the background and ranked accordingly. Once done, place in actual transaction table.

Thanks for checking, please assist.

Bregds/Venkat

 

[NARAYANK991]

Hi Venkat ,

Can you clarify , preferably with one or more examples , what this means ?

(in case of same rates, duplicate ranks with continuity is also needed)

Can you try the following array entered formula ?

=MATCH(K4,SMALL(IF((Sheet1!$B$4:$B$7=Sheet1!H4)*(Sheet1!J4>=Sheet1!$D$4:$D$7)*(Sheet1!J4<=Sheet1!$E$4:$E$7),(Sheet1!$F$4:$F$7),9999999),ROW($A$1:$A$4)),0)

Narayan

 

[Venkat76]

Dear Narayan - Thanks a lot for the support. On the clarification part, for example if in a group there are 5 rates -

Vendor A - 100
Vendor B - 100
Vendor C - 150
Vendor D - 200
Vendor E - 250

Excel Rank formula (ascending) would give 1,1,3,4,5 - therefore, due to duplicate 1, the continuity gets missed and jumps to 3. Whereas, the desired result would be 1,1,2,3,4 - in other words 'duplicate ranks with continuity'.

Just a query - the Row(A1:A4) in your formula - it is otherwise not part of the table ranges in the example - maybe it is supposed to be that way? And should I change it to any other range when I test this formula to a large dataset..? Please advise.

Thanks
Venkat

 

[NARAYANK991]

Hi Venkat ,

Can you upload a workbook which has enough data for testing all possibilities ?

Narayan

 

[Venkat76]

Hi Narayan - apologies for delayed response, was travelling, did not log on. Team is generating the database, shall review for deviation possibilities, if any, and revert. Thanks for the support.
Bregds/Venkat