Finding the top 3 DISTINCT records/indexes from the Table

[Rituraj]

Friends,

I need this urgent help. I need to find out the top three demand values from the table attached with this note. Kindly NOTE That there may be repetitive entities of the same account code in the array...But i need to consider that in formulating the final top 3 out of the 9 demand entries in the table for each row.

So in a nutshell, I need to get the highest, second highest and third highest (DIFFERENT) account codes for the array(Array is a single row). Kindly use the formula and it is jsut a snapshot of excel sheet, which runs into thousands...

THANKS A TON!
Raj

[NBVC]

What are basing the top 3 on?

Can you fill in some of the results manually to show what you are requiring?

[Rituraj]

Apologies for not being clear with my question..I have filled in the columns Y-> AD as the expected answer.

TO quote: In ROW no 4, the top 3 key accounts will be T-Mobile ..if I look at the top three values between D4 and U4. But the answer must be that the T-MObile is the highest DISTINCT customer and second is "National service center" and the third would be "TELECOM EQUIPMENT PTE LTD".

Though the first three max values belong to T-Mobile but the answer must drive to find top 3 distinct customers for each such array(ROW)

THANKSSSSSSSSSSS!
Raj

[NBVC]

Ok...

In Y4:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear around the formula.

in Z4:

Please Login or Register  to view this content.

confirmed with ENTER as normal.

in AA4:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear around the formula.

in AB4:

Please Login or Register  to view this content.

confirmed with ENTER as normal.

in AC4:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear around the formula.

in AD4:

Please Login or Register  to view this content.

confirmed with ENTER as normal.

Then each copied down as far as you need.

Note: These formulas assume each row contains no duplicate Consensus Demand numbers.... If they can.. then some of the formulas will be more complex.

[Rituraj]

U r awesome and this is more than social service ..! Great going pal! U just saved my whole day!

Thanks,
Raj

[shg]

Rituraj, please don't quote whole posts -- it's just clutter.