Assign a badge/ID for reapted the same records under condition

[Jorgi]

Hello, I'm looking for a way (formula or macro) to assign ID or batch number under condition e.g., if the same records (AAA) in the same column is repeated 20 times or more then the first 10 repeated records (AAA) will get ID: A1 and the others over 10 and more will get ID: A2 but if the same record is not repeated more than 20 times in one same column then all the records will get ID: A1. Thank you for any help

 

[dreid1011]

Here's a start, but how do you determine what the ID number will be for each unique record? IE: What ID does BBB get? CCC? DDD?

Book1
	A	B	C	D	E
1	AAA	A1		AAA	A1
2	AAA	A1		AAA	A1
3	AAA	A1		AAA	A1
4	AAA	A1		AAA	A1
5	AAA	A1		AAA	A1
6	AAA	A1		AAA	A1
7	AAA	A1		AAA	A1
8	AAA	A1		AAA	A1
9	AAA	A1		AAA	A1
10	AAA	A1		AAA	A1
11	AAA	A2		AAA	A1
12	AAA	A2		AAA	A1
13	AAA	A2		AAA	A1
14	AAA	A2		AAA	A1
15	AAA	A2		AAA	A1
16	AAA	A2
17	AAA	A2
18	AAA	A2
19	AAA	A2
20	AAA	A2
21	AAA	A2
22	AAA	A2
23	AAA	A2
24	AAA	A2
25	AAA	A2
26	AAA	A2
27	AAA	A2
28	AAA	A2
29	AAA	A2
30	AAA	A2
Sheet2

Cell Formulas
Range	Formula
E1:E15,B1:B30	E1	=IF(COUNTIF(D:D,D1)<20,"A1",IF(AND(COUNTIF(D:D,D1)>=20,COUNTIF($A$1:D1,D1)<=10),"A1","A2"))

 

[Jorgi]

Here's a start, but how do you determine what the ID number will be for each unique record? IE: What ID does BBB get? CCC? DDD?

Thank you, in case BBB, CCC, DDD... ZZZ it will be the same logic e.g., ZZZ ID: Z1 for all if reapeted less than 20 times and if ZZZ is repeated more than 20 times then first 10 ZZZs ID Z1 and over 10 and more ID:Z2. In one the same column can be mixture of the reapeated records so not only AAA can be AAA to ZZZ.

 

[dreid1011]

Okay, how about this:

Book1
	A	B
169	VVV	V1
170	NNN	N1
171	PPP	P1
172	VVV	V1
173	EEE	E1
174	FFF	F1
175	UUU	U1
176	III	I1
177	JJJ	J1
178	AAA	A1
179	YYY	Y1
180	AAA	A1
181	QQQ	Q1
182	CCC	C1
183	OOO	O1
184	CCC	C1
185	QQQ	Q1
186	GGG	G1
187	HHH	H1
188	QQQ	Q1
189	EEE	E1
190	RRR	R1
191	LLL	L1
192	OOO	O1
193	TTT	T1
194	HHH	H1
195	MMM	M1
196	EEE	E1
197	VVV	V2
198	RRR	R1
199	QQQ	Q1
200	OOO	O1
201	VVV	V2
202	WWW	W1
Sheet2

Cell Formulas
Range	Formula
B169:B202	B169	=IF(COUNTIF(A:A,A169)<20,LEFT(A169,1)&"1",IF(AND(COUNTIF(A:A,A169)>=20,COUNTIF($A$1:A169,A169)<=10),LEFT(A169,1)&"1",LEFT(A169,1)&"2"))

I used A1:A300 and filled with random values. As you can see, VVV is the one with 20 or more entries:

Book1
	D	E
1	AAA	13
2	BBB	8
3	CCC	13
4	DDD	8
5	EEE	17
6	FFF	12
7	GGG	13
8	HHH	10
9	III	9
10	JJJ	6
11	KKK	9
12	LLL	13
13	MMM	11
14	NNN	13
15	OOO	11
16	PPP	8
17	QQQ	13
18	RRR	13
19	SSS	12
20	TTT	8
21	UUU	12
22	VVV	21
23	WWW	13
24	XXX	12
25	YYY	9
26	ZZZ	13
Sheet2

Cell Formulas
Range	Formula
E1:E26	E1	=COUNTIF($A$1:$A$300,D1)

 

[Jorgi]

Okay, how about this:

That is exactly what I was looking for thank you so much for your help

 

[dreid1011]

You're welcome, and happy to help.