Another Ranking problem

[khelgadi]

Dear All, Attached file contains the data table and desired output. You are requested to help me setting the formula.

 

[vijaySharma]

Khelgadi,

use the below formula to get the results.

=C3&COUNTIF($C$3:C3,C3)

 

[khelgadi]

Thanks Vijay Sharma... But this solution is not valid if I sort data.
There should be reference taken from Column A, Which I could not do.
Can you still help me out?

 

[webmax]

hi i have sort the file it is working fine.

 

[khelgadi]

Dear Webmax, the initial 2 columns are part of bigger database which needs to sort/ filter etc. the numbering system is required to provide unique numbering to each book. if you sort on Column B / Column C value, the numbering would change. There should not be change in the Book Number.

 

[webmax]

can u upload the same file after sorting and issues by marking colour

 

[khelgadi]

I hope attached file clarifies the doubt. And thanks for taking interest....

 

[webmax]

hi i have sorted the category but it is working fine i am attaching the file.

 

[Haseeb A]

Hello,

If the ID is always in the same format, starting with M & four digits, try this in D3, then copy down.

=C3&COUNTIFS(A:A,"<"&A3,C:C,C3)+1

If the ID starting alpha is different & last four character are digits, try this

=C3&SUMPRODUCT((C$3:C$22=C3)*(RIGHT(A3,4)+0>RIGHT(A$3:A$22,4)+0))+1

 

[khelgadi]

hi i have sorted the category but it is working fine i am attaching the file.

Dear Webmax,
You copy the desired output in one column as values. Give formula to adjacent column. If you sort on Column B / C, you'll find the difference in These two columns.
Thanks for Helping.

 

[khelgadi]

Hello,

If the ID is always in the same format, starting with M & four digits, try this in D3, then copy down.

=C3&COUNTIFS(A:A,"<"&A3,C:C,C3)+1

If the ID starting alpha is different & last four character are digits, try this

=C3&SUMPRODUCT((C$3:C$22=C3)*(RIGHT(A3,4)+0>RIGHT(A$3:A$22,4)+0))+1

Dear Haseeb,
Thanks a lot, it Worked... I couldn't use Sumproduct function earlier, never thought that it can be used like this...
Thanks very much.:D

 

[NARAYANK991]

Hi ,

The formula can be reduced to :

=C3&SUMPRODUCT((C$3:C$22=C3)*(A3>A$3:A$22))+1

since your data in column A is numeric , and the M is appearing only through formatting.

Narayan