Sequence number based on value of another column

[dhen21dx]

Hello,

Just wanna ask, what is the best way to achieved this kind of Item No sequence based on the value of Item.

Item No	Code1	Item	Country
1​	CA	CA8	AT1
2​	CA	CA8	BE1
3​	CA	CA8	CN1
4​	CA	CA8	CNB3
1​	GL	GL302	AT1
2​	GL	GL302	BE1
3​	GL	GL302	CNB3
4​	GL	GL302	CNDD
5​	GL	GL302	CZ1
6​	GL	GL302	FR1
7​	GL	GL302	GB1
8​	GL	GL302	HKTCP1
9​	GL	GL302	MY1
1​	GL	GL353	AT1
2​	GL	GL353	BE1
3​	GL	GL353	CNB3
4​	GL	GL353	CNDD
1​	US	US138	HK1
2​	US	US138	HKDD
3​	US	US138	HKTCP1
4​	US	US138	HKTT
5​	US	US138	HU1
6​	US	US138	IN1
7​	US	US138	IT1
8​	US	US138	JP1
9​	US	US138	KR1
10​	US	US138	KWAI CHUNG
11​	US	US138	MX1
12​	US	US138	MY1
13​	US	US138	MY10
14​	US	US138	MY11
15​	US	US138	MYDD
16​	US	US138	NL1
17​	US	US138	NLDD
18​	US	US138	PH1
19​	US	US138	PHDD
20​	US	US138	PHTT
21​	US	US138	RO1
22​	US	US138	SE1
23​	US	US138	SG1
24​	US	US138	TH1
1​	US	US139	AT1
2​	US	US139	BE1
3​	US	US139	BR1
4​	US	US139	CA1
5​	US	US139	CN1
6​	US	US139	CNB3
7​	US	US139	CNDD
8​	US	US139	CNSGMT
1​	US	US142	AT1
2​	US	US142	BE1
3​	US	US142	CN1

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you mean?

22 03 07.xlsm
	A	B	C	D
1	Item No	Code1	Item	Country
2	1	CA	CA8	AT1
3	2	CA	CA8	BE1
4	3	CA	CA8	CN1
5	4	CA	CA8	CNB3
6	1	GL	GL302	AT1
7	2	GL	GL302	BE1
8	3	GL	GL302	CNB3
9	4	GL	GL302	CNDD
10	5	GL	GL302	CZ1
11	6	GL	GL302	FR1
12	7	GL	GL302	GB1
13	8	GL	GL302	HKTCP1
14	9	GL	GL302	MY1
15	1	GL	GL353	AT1
16	2	GL	GL353	BE1
17	3	GL	GL353	CNB3
18	4	GL	GL353	CNDD
19	1	US	US138	HK1
20	2	US	US138	HKDD
21	3	US	US138	HKTCP1
22	4	US	US138	HKTT
23	5	US	US138	HU1
24	6	US	US138	IN1
25	7	US	US138	IT1
26	8	US	US138	JP1
27	9	US	US138	KR1
28	10	US	US138	KWAI CHUNG
29	11	US	US138	MX1
30	12	US	US138	MY1
31	13	US	US138	MY10
32	14	US	US138	MY11
33	15	US	US138	MYDD
34	16	US	US138	NL1
35	17	US	US138	NLDD
36	18	US	US138	PH1
37	19	US	US138	PHDD
38	20	US	US138	PHTT
39	21	US	US138	RO1
40	22	US	US138	SE1
41	23	US	US138	SG1
42	24	US	US138	TH1
43	1	US	US139	AT1
44	2	US	US139	BE1
45	3	US	US139	BR1
46	4	US	US139	CA1
47	5	US	US139	CN1
48	6	US	US139	CNB3
49	7	US	US139	CNDD
50	8	US	US139	CNSGMT
51	1	US	US142	AT1
52	2	US	US142	BE1
53	3	US	US142	CN1
Item No

Cell Formulas
Range	Formula
A2:A53	A2	=COUNTIF(C$2:C2,C2)

 

[earthworm]

t

Hello,

Just wanna ask, what is the best way to achieved this kind of Item No sequence based on the value of Item.

Item No	Code1	Item	Country
1​	CA	CA8	AT1
2​	CA	CA8	BE1
3​	CA	CA8	CN1
4​	CA	CA8	CNB3
1​	GL	GL302	AT1
2​	GL	GL302	BE1
3​	GL	GL302	CNB3
4​	GL	GL302	CNDD
5​	GL	GL302	CZ1
6​	GL	GL302	FR1
7​	GL	GL302	GB1
8​	GL	GL302	HKTCP1
9​	GL	GL302	MY1
1​	GL	GL353	AT1
2​	GL	GL353	BE1
3​	GL	GL353	CNB3
4​	GL	GL353	CNDD
1​	US	US138	HK1
2​	US	US138	HKDD
3​	US	US138	HKTCP1
4​	US	US138	HKTT
5​	US	US138	HU1
6​	US	US138	IN1
7​	US	US138	IT1
8​	US	US138	JP1
9​	US	US138	KR1
10​	US	US138	KWAI CHUNG
11​	US	US138	MX1
12​	US	US138	MY1
13​	US	US138	MY10
14​	US	US138	MY11
15​	US	US138	MYDD
16​	US	US138	NL1
17​	US	US138	NLDD
18​	US	US138	PH1
19​	US	US138	PHDD
20​	US	US138	PHTT
21​	US	US138	RO1
22​	US	US138	SE1
23​	US	US138	SG1
24​	US	US138	TH1
1​	US	US139	AT1
2​	US	US139	BE1
3​	US	US139	BR1
4​	US	US139	CA1
5​	US	US139	CN1
6​	US	US139	CNB3
7​	US	US139	CNDD
8​	US	US139	CNSGMT
1​	US	US142	AT1
2​	US	US142	BE1
3​	US	US142	CN1

Try this

Book1
	A	B	C	D
1	tem No	Code1	Item	Country
2	1	CA	CA8	AT1
3	2	CA	CA8	BE1
4	3	CA	CA8	CN1
5	4	CA	CA8	CNB3
6	1	GL	GL302	AT1
7	2	GL	GL302	BE1
8	3	GL	GL302	CNB3
9	4	GL	GL302	CNDD
10	5	GL	GL302	CZ1
11	6	GL	GL302	FR1
12	7	GL	GL302	GB1
13	8	GL	GL302	HKTCP1
14	9	GL	GL302	MY1
15	1	GL	GL353	AT1
16	2	GL	GL353	BE1
17	3	GL	GL353	CNB3
18	4	GL	GL353	CNDD
19	1	US	US138	HK1
20	2	US	US138	HKDD
21	3	US	US138	HKTCP1
22	4	US	US138	HKTT
23	5	US	US138	HU1
24	6	US	US138	IN1
25	7	US	US138	IT1
26	8	US	US138	JP1
27	9	US	US138	KR1
28	10	US	US138	KWAI CHUNG
29	11	US	US138	MX1
30	12	US	US138	MY1
31	13	US	US138	MY10
32	14	US	US138	MY11
33	15	US	US138	MYDD
34	16	US	US138	NL1
35	17	US	US138	NLDD
36	18	US	US138	PH1
37	19	US	US138	PHDD
38	20	US	US138	PHTT
39	21	US	US138	RO1
40	22	US	US138	SE1
41	23	US	US138	SG1
42	24	US	US138	TH1
43	1	US	US139	AT1
44	2	US	US139	BE1
45	3	US	US139	BR1
46	4	US	US139	CA1
47	5	US	US139	CN1
48	6	US	US139	CNB3
49	7	US	US139	CNDD
50	8	US	US139	CNSGMT
51	1	US	US142	AT1
52	2	US	US142	BE1
53	3	US	US142	CN1
Sheet2

Cell Formulas
Range	Formula
A2:A53	A2	=COUNTIF(C$2:C2,C2)

 

[Peter_SSs]

@dhen21dx
Didn't realise initially that this was posted in the Microsoft Access forum. Should it be moved to the Excel Questions forum?

 

[Micron]

If this is an Access problem and it involves a single update on a table, then the only solution I see is a UDF (user defined function), or maybe a subquery and I really struggle with the latter most of the time. To make a UDF worthwhile methinks there ought to be a LOT of records that need updating in order to create the code, otherwise faster to just type in the required values. If this to generate a new value for each new record, then it's much easier to do with a form. With a single record view form, the form cycles through the records and can do the update one by one as well as create the required value for new records.

The problem with a SELECT query calling a UDF that tries to calculate and update the table, or an UPDATE query that runs a function that generates the number, the query will load records and create the same number for all records. The reason for that is the loaded query records won't update when the table is updated. So maybe a UDF that loads and updates a recordset can work if the rs outer loop compares the current record [Item] value against one that is stored in a variable at the beginning of the loop. When equal, the inner loop updates and moves next. When not, the inner stops and the outer moves next. Rinse and repeat.

If this is an Excel problem, well then I guess I wasted my time and everyone else's.

 

[Micron]

If this turns out to be Access related, I have a function that seems to work & will post the code if needed.

 

[dhen21dx]

Hello, sorry i was off for a while, I think i have correct category Microsoft Access, this post is for Ms Access

 

[Micron]

Then perhaps

Public Function GetIncrement()
Dim rs As DAO.Recordset
Dim strItem As String, strNewItem As String
Dim i As Integer

On Error GoTo errHandler

Set rs = CurrentDb.OpenRecordset("SELECT * FROM dhen WHERE Item_No Is Null ORDER BY Item;")
If Not (rs.EOF And rs.BOF) Then
   ''rs.MoveLast
   rs.MoveFirst
   Do While Not rs.EOF
      strItem = rs.Fields("Item")
      strNewItem = rs.Fields("Item")
      Do Until strNewItem <> strItem
         rs.Edit
         rs.Fields("Item_No") = Nz(DMax("Item_No", "dhen", "Item='" & strItem & "'"), 0) + 1
         rs.Update
         rs.MoveNext
         'could now be rs.EOF; test rather than raise error trying to assign a value to strItem:
         If Not rs.EOF Then
            strItem = rs.Fields("Item")
         Else
            Exit Function
         End If
      Loop
   Loop
Else
   MsgBox "No records need updating."
End If

exitHere:
Set rs = Nothing
Exit Function

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Function

The table is like (but 52 rows from OP)
dhen

Item_No	Code1	Item	Country
​	CA	CA8	AT1
​	CA	CA8	BE1
​	CA	CA8	CN1
​	CA	CA8	CNB3
​	GL	GL302	AT1
​	GL	GL302	BE1
​	GL	GL302	CNB3
​	GL	GL302	CNDD
​	GL	GL302	CZ1

Records pasted from post are not stored in my Access table exactly as posted but the sequencing seems to be accurate anyway, and the result can be sorted on the sequence number if required.
dhen

Item_No	Code1	Item	Country
4​	CA	CA8	AT1
1​	CA	CA8	BE1
2​	CA	CA8	CN1
3​	CA	CA8	CNB3
2​	GL	GL302	AT1
3​	GL	GL302	BE1
4​	GL	GL302	CNB3
5​	GL	GL302	CNDD
6​	GL	GL302	CZ1
7​	GL	GL302	FR1
8​	GL	GL302	GB1
9​	GL	GL302	HKTCP1
1​	GL	GL302	MY1

To clarify the code comment re: EOF - if MoveNext during the execution of a loop places the recordset cursor at EOF, trying to assign a value to the variable will generate an error. One could trap for that error in the error handler, but I prefer to prevent it from occurring if possible. Whether or not there is a better start to the loop other than Do While Not rs.EOF I don't know - I never gave that any thought.

 

[dhen21dx]

Hi Micron thank you, let me test it now in Access.

 

[Peter_SSs]

this post is for Ms Access

OK, thanks for the confirmation.