count duplicate data in column

[Dumy]

My goal is to return the unique counting
How to count the number of occurrences of data that is duplicated in the column, but so to a group of duplicates has its unique sequential number.

There is 4 case,
Case1/Case3 is sorted text/values
Case2/Case4 is unsorted text/values

please you see example attach file

[ChemistB]

This formula works for all 4 of your examples

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B12,0),MATCH(B2:B12,B2:B12,0))>0,1))

[Dumy]

ChemistB, Thanks for answer

Maybe I misspelled topic title?
I'm sorry but this is not correct the answer for me, or I do not understand something.
This formula returns the number 4 or Unique counting numbers. I need a count for each unique data the same number

Case1

Count - Name Sorted
1 A
1 A
2 B
2 B
2 B
3 C
3 C
4 D
4 D
4 D
4 D

Case2

Count - Name UNSORTED
1 B
2 A
1 B
1 B
1 B
2 A
2 A
3 D
4 C
4 C
3 D


Please see the attachment again
I am, for each identified case enrolled the desired results

If possible without VBA?

regards

[newdoverman]

The first part of your problem: Values in A2:A12 enter in B2 and fill down:

=IF(A2<>A1,MAX($B$1:B1)+1,B1)

	A	B
2	A	1
3	A	1
4	B	2
5	B	2
6	B	2
7	C	3
8	C	3
9	D	4
10	D	4
11	D	4
12	D	4

[JeteMc]

Here is an array formula (meaning that it needs to be entered with Crtl + Shift + Enter) that will do what you want:

=IF(OR(B$2:B2=B3),VLOOKUP(B3,B$2:C2,2,FALSE),C2+1)

It needs to be noted that either the first value in the column will always be manually entered as 1 (as I did in columns J and M), or you put an empty row of cells under the column headers and use the formula throughout (as I did in columns C and F).
After entering (as described) the formula, drag (copy) the formula down as far as needed. In the attached file the columns with the heading "Formula count" contain the values produced by the formula, which are identical to the ones provided originally.

Copy of countduplicate.xlsx

Hope this helps.

[newdoverman]

This formula will solve both of the problems that you gave in msg# 3
If the data is in A2 to A12 and C2 to C12
Enter this in B2 and fill down:

=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,INDEX(B$1:B1,MATCH(A2,A$1:A1,0)))

Enter this in C2 and fill down (same formula as above with references changed to suit new column)

=IF(COUNTIF($C$2:C2,C2)=1,MAX($D$1:D1)+1,INDEX($D$1:D1,MATCH(C2,$C$1:C1,0)))

	A	B	C	D
2	a	1	b	1
3	a	1	a	2
4	b	2	b	1
5	b	2	b	1
6	b	2	b	1
7	c	3	a	2
8	c	3	a	2
9	d	4	d	3
10	d	4	c	4
11	d	4	c	4
12	d	4	d	3

[azumi]

Pls find the file attach, for SORTED values Im borrow from newdoverman.....

thanks

[samba_ravi]

deleted deleted

[Dumy]

Thank you very much guys
All formulas are doing great, but I was the most appropriate single formula of @newdoverman which works on all four cases
It's great that other formulas can be used for some cases

I respect everyone
(problem solved)

[newdoverman]

Thank you for the feedback.