Sequence number based on value of another column

D

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
166
Hello,

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

Item NoCode1ItemCountry
1​
CACA8AT1
2​
CACA8BE1
3​
CACA8CN1
4​
CACA8CNB3
1​
GLGL302AT1
2​
GLGL302BE1
3​
GLGL302CNB3
4​
GLGL302CNDD
5​
GLGL302CZ1
6​
GLGL302FR1
7​
GLGL302GB1
8​
GLGL302HKTCP1
9​
GLGL302MY1
1​
GLGL353AT1
2​
GLGL353BE1
3​
GLGL353CNB3
4​
GLGL353CNDD
1​
USUS138HK1
2​
USUS138HKDD
3​
USUS138HKTCP1
4​
USUS138HKTT
5​
USUS138HU1
6​
USUS138IN1
7​
USUS138IT1
8​
USUS138JP1
9​
USUS138KR1
10​
USUS138KWAI CHUNG
11​
USUS138MX1
12​
USUS138MY1
13​
USUS138MY10
14​
USUS138MY11
15​
USUS138MYDD
16​
USUS138NL1
17​
USUS138NLDD
18​
USUS138PH1
19​
USUS138PHDD
20​
USUS138PHTT
21​
USUS138RO1
22​
USUS138SE1
23​
USUS138SG1
24​
USUS138TH1
1​
USUS139AT1
2​
USUS139BE1
3​
USUS139BR1
4​
USUS139CA1
5​
USUS139CN1
6​
USUS139CNB3
7​
USUS139CNDD
8​
USUS139CNSGMT
1​
USUS142AT1
2​
USUS142BE1
3​
USUS142CN1
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
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
ABCD
1Item NoCode1ItemCountry
21CACA8AT1
32CACA8BE1
43CACA8CN1
54CACA8CNB3
61GLGL302AT1
72GLGL302BE1
83GLGL302CNB3
94GLGL302CNDD
105GLGL302CZ1
116GLGL302FR1
127GLGL302GB1
138GLGL302HKTCP1
149GLGL302MY1
151GLGL353AT1
162GLGL353BE1
173GLGL353CNB3
184GLGL353CNDD
191USUS138HK1
202USUS138HKDD
213USUS138HKTCP1
224USUS138HKTT
235USUS138HU1
246USUS138IN1
257USUS138IT1
268USUS138JP1
279USUS138KR1
2810USUS138KWAI CHUNG
2911USUS138MX1
3012USUS138MY1
3113USUS138MY10
3214USUS138MY11
3315USUS138MYDD
3416USUS138NL1
3517USUS138NLDD
3618USUS138PH1
3719USUS138PHDD
3820USUS138PHTT
3921USUS138RO1
4022USUS138SE1
4123USUS138SG1
4224USUS138TH1
431USUS139AT1
442USUS139BE1
453USUS139BR1
464USUS139CA1
475USUS139CN1
486USUS139CNB3
497USUS139CNDD
508USUS139CNSGMT
511USUS142AT1
522USUS142BE1
533USUS142CN1
Item No
Cell Formulas
RangeFormula
A2:A53A2=COUNTIF(C$2:C2,C2)
 
Upvote 0
t
dhen21dx said:
Hello,

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

Item NoCode1ItemCountry
1​
CACA8AT1
2​
CACA8BE1
3​
CACA8CN1
4​
CACA8CNB3
1​
GLGL302AT1
2​
GLGL302BE1
3​
GLGL302CNB3
4​
GLGL302CNDD
5​
GLGL302CZ1
6​
GLGL302FR1
7​
GLGL302GB1
8​
GLGL302HKTCP1
9​
GLGL302MY1
1​
GLGL353AT1
2​
GLGL353BE1
3​
GLGL353CNB3
4​
GLGL353CNDD
1​
USUS138HK1
2​
USUS138HKDD
3​
USUS138HKTCP1
4​
USUS138HKTT
5​
USUS138HU1
6​
USUS138IN1
7​
USUS138IT1
8​
USUS138JP1
9​
USUS138KR1
10​
USUS138KWAI CHUNG
11​
USUS138MX1
12​
USUS138MY1
13​
USUS138MY10
14​
USUS138MY11
15​
USUS138MYDD
16​
USUS138NL1
17​
USUS138NLDD
18​
USUS138PH1
19​
USUS138PHDD
20​
USUS138PHTT
21​
USUS138RO1
22​
USUS138SE1
23​
USUS138SG1
24​
USUS138TH1
1​
USUS139AT1
2​
USUS139BE1
3​
USUS139BR1
4​
USUS139CA1
5​
USUS139CN1
6​
USUS139CNB3
7​
USUS139CNDD
8​
USUS139CNSGMT
1​
USUS142AT1
2​
USUS142BE1
3​
USUS142CN1
Click to expand...


Try this

Book1
ABCD
1tem NoCode1ItemCountry
21CACA8AT1
32CACA8BE1
43CACA8CN1
54CACA8CNB3
61GLGL302AT1
72GLGL302BE1
83GLGL302CNB3
94GLGL302CNDD
105GLGL302CZ1
116GLGL302FR1
127GLGL302GB1
138GLGL302HKTCP1
149GLGL302MY1
151GLGL353AT1
162GLGL353BE1
173GLGL353CNB3
184GLGL353CNDD
191USUS138HK1
202USUS138HKDD
213USUS138HKTCP1
224USUS138HKTT
235USUS138HU1
246USUS138IN1
257USUS138IT1
268USUS138JP1
279USUS138KR1
2810USUS138KWAI CHUNG
2911USUS138MX1
3012USUS138MY1
3113USUS138MY10
3214USUS138MY11
3315USUS138MYDD
3416USUS138NL1
3517USUS138NLDD
3618USUS138PH1
3719USUS138PHDD
3820USUS138PHTT
3921USUS138RO1
4022USUS138SE1
4123USUS138SG1
4224USUS138TH1
431USUS139AT1
442USUS139BE1
453USUS139BR1
464USUS139CA1
475USUS139CN1
486USUS139CNB3
497USUS139CNDD
508USUS139CNSGMT
511USUS142AT1
522USUS142BE1
533USUS142CN1
Sheet2
Cell Formulas
RangeFormula
A2:A53A2=COUNTIF(C$2:C2,C2)
 
Upvote 0
@dhen21dx
Didn't realise initially that this was posted in the Microsoft Access forum. Should it be moved to the Excel Questions forum?
 
Upvote 0
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. :oops:
 
Upvote 0
If this turns out to be Access related, I have a function that seems to work & will post the code if needed.
 
Upvote 0
Hello, sorry i was off for a while, I think i have correct category Microsoft Access, this post is for Ms Access
 
Upvote 0
Then perhaps
VBA Code: 
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_NoCode1ItemCountry
​
CACA8AT1
​
CACA8BE1
​
CACA8CN1
​
CACA8CNB3
​
GLGL302AT1
​
GLGL302BE1
​
GLGL302CNB3
​
GLGL302CNDD
​
GLGL302CZ1
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_NoCode1ItemCountry
4​
CACA8AT1
1​
CACA8BE1
2​
CACA8CN1
3​
CACA8CNB3
2​
GLGL302AT1
3​
GLGL302BE1
4​
GLGL302CNB3
5​
GLGL302CNDD
6​
GLGL302CZ1
7​
GLGL302FR1
8​
GLGL302GB1
9​
GLGL302HKTCP1
1​
GLGL302MY1
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.
 
Last edited:
Upvote 0
Solution
You must log in or register to reply here.

Forum statistics

Threads
1,216,515
Messages
6,131,111
Members
449,621
Latest member
feaugcruz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top