Identify "nth" unique entry in a list

[ellison]

Hi, is it possible to identify the "nth" unique entry in a list?
In other words: Column A has 8 different unique entries, some duplicated.
So for rows 7 to 8 where the Info is "charlie":
Col B "nth unique entry": charlie is the 5th unique entry on the list (i.e. 1,2,alpha,beta, charlie)
Col C "nth with count": there are 2 duplicates of charlie, so the 1st one (row 8) will be 5.1. And the second row (row 9) 5.2
If it helps, the info in Column is sorted A-Z

Book3
	A	B	C
1	Info (a-z)	"nth unique entry"	"nth with count"
2	1	1	1.1
3	2	2	2.1
4	2	2	2.2
5	alpha	3	3.1
6	beta	4	4.1
7	charlie	5	5.1
8	charlie	5	5.2
9	delta	6	6.1
10	echo	7	7.1
11	echo	7	7.1
12	echo	7	7.1
13	foxtrot	8	8.1
14	foxtrot	8	8.2
Sheet1

Hope this helps

 

[Legacy 143009]

For B2 formula will be:

=SUM(1/COUNTIF($A$2:A2;$A$2:A2))

I didn't understand column C

 

[ellison]

For B2 formula will be:

=SUM(1/COUNTIF($A$2:A2;$A$2:A2))

I didn't understand column C

Sorry, I tried insertinug the formula into cell B2, but Excel is giving me an error message saying there's a problem with the formula....

 

[ellison]

For Column C...

If Rows 7 and 8, the info is for Charlie.
So the first occurrence in Row 7 adds ".1" onto the end of the digit from Column B: "nth unique occurrence in a list".
And Row 8 would be the second occurrence. Therefore it would add ".2" to the "nth unique occurrence in a list".
Hope that helps

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Info (a-z)	"nth unique entry"	"nth with count"
2	1	1	1.1
3	2	2	2.1
4	2	2	2.2
5	alpha	3	3.1
6	beta	4	4.1
7	charlie	5	5.1
8	charlie	5	5.2
9	delta	6	6.1
10	echo	7	7.1
11	echo	7	7.2
12	echo	7	7.3
13	foxtrot	8	8.1
14	foxtrot	8	8.2
15
Data

Cell Formulas
Range	Formula
B2:C14	B2	=LET(d,FILTER(A2:A100,A2:A100<>""),r,ROWS(d),u,UNIQUE(d),x,XMATCH(d,u),HSTACK(x,x&"."&MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(d=TOROW(d)),SEQUENCE(r,,,0))))
Dynamic array formulas.

 

[Legacy 143009]

Sorry, I tried insertinug the formula into cell B2, but Excel is giving me an error message saying there's a problem with the formula....

Oh replace ; with ,

 

[ellison]

Oh replace ; with ,

Great, that got Col B sorted huge thanks

(And Mr Fluff's version above has Columns B & C soprted, so I 'll stick that down as the solution).

Thanks again

 

[ellison]

How about

Fluff.xlsm
	A	B	C
1	Info (a-z)	"nth unique entry"	"nth with count"
2	1	1	1.1
3	2	2	2.1
4	2	2	2.2
5	alpha	3	3.1
6	beta	4	4.1
7	charlie	5	5.1
8	charlie	5	5.2
9	delta	6	6.1
10	echo	7	7.1
11	echo	7	7.2
12	echo	7	7.3
13	foxtrot	8	8.1
14	foxtrot	8	8.2
15
Data

Cell Formulas
Range	Formula
B2:C14	B2	=LET(d,FILTER(A2:A100,A2:A100<>""),r,ROWS(d),u,UNIQUE(d),x,XMATCH(d,u),HSTACK(x,x&"."&MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(d=TOROW(d)),SEQUENCE(r,,,0))))
Dynamic array formulas.

Massive thanks, that works beautifully (& is working really quickly on larger sets of data too...)

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ellison]

This is [ushing my luck massively, but is there a way that the digits that the counts give to inclue the leading zero's....

eg 0001 as opposed to 1
and 0001.1 as opposed to 1.1

(That would enable us to do extra sorting using these results later on).