count if duplication in one column with relation to another column value

[mazan2010]

hello everyone

i have data in column A for different country every country have different city in column B
the problem it i need to count every same letter in column B related to same value in column A then make result as thew following
count repeated value in column B till reach number of 10 repeat then start again to count from 1 till end ( meaning make it group each consist of 10)

example to be clear

if make filter in column A for NOOOOOOOOOOO Value we can find 53 row and when look to column B we can find letter is F,G,T

for T letter , its reaped 34 times so i need result in column C as the following each 10 will be as the following
first 10 T it will be T
second 10 it will be T1
Third it will be T2
Fourth it will be T3
and soon



for G letter its only repeated 3 time so it will be G without any number

for F letter its repeated 16 times so it will be F for first 10 then F2 for remaining repeat

i try many solution to use helper column but still cant find the right solution
thanks for your help and support

[nick.williams]

Try in C2, copied down:

=B2&LET(main,$A$2:$A$342,letters,$B$2:$B$342,x,SUM(IF((main=A2)*(letters=B2)*(ROW(main)<=ROW(A2)),1,0)),IF(x<=10,"",CEILING(x/10,1)-1))

[Gregb11]

Another possible solution:

In C2 and copied down:

=B2&LET(a,ROUNDDOWN(COUNTIFS(A$2:A2,A2,B$2:B2,B2)/10-0.01,0),IF(a=0,"",a))

[wk9128]

another option
Cell C2 formula , Drag down

Please Login or Register  to view this content.

[mazan2010]

thank you so much for all all work very well

[nick.williams]

I noticed an error with mine - it should have had CEILING(x/10,1)-1 instead of FLOOR(x/10,1) at the end. I have edited my post to show the correct version.

[Gregb11]

You're welcome. And thanks for the rep.

[wk9128]

@mazan2010 You're Welcome. Glad to help . Thank You for the feedback and rep.