Increasing Number sequence based on cell value in a different column

Theemeadelis

New Member
Joined
Jul 10, 2023
Messages
20
Office Version
  1. 365
Platform
  1. Windows
I have a large data set that I need to set an increasing number sequence in column A based on the value showing in column D.

i.e. The number in column A increases by 1 each time the value in column D changes:

ABCD
1001AX11172ILC010377, VSC003830ENT002281
1001AX8170ILC006062, VNC001394ENT002281
1001AX8171ILC006386, VNC001395ENT002281
1001AX8263ILC006275ENT002281
1001AX8271ILC006299ENT002281
1001AX8301ILC006362ENT002281
1001AX9933ILC008840, VSC002400ENT002281
1001AX9935ILC008842, VSC002402ENT002281
1002AX8405ILC006620, VSC002028ENT002936
1002AX9255ILC008021, VSC002197ENT002936
1002AX9608ILC008458ENT002936
1003AX9267ILC008037, VSC002043ENT002955
1003AX9289ILC008061ENT002955
1003AX9310ILC008085, VSC002066ENT002955
1003AX9317ILC008093, VSC002067ENT002955
1003AX9385ILC008191ENT002955
1004AX10635ILC009664, VNC002680ENT002957
1004AX9270ILC008040, VEC001321, VSC002045ENT002957
1004AX9315ILC008090, VEC001106, VSC003158ENT002957
1004AX9528ILC008371, VNC002175, VSC002181ENT002957
1005AX9523ILC008363, VEC001036, VNC002463ENT003254
1006AX12113ILC011830, VSC003785ENT003550
1007AX10294ILC009258ENTZZZZZ84
1007AX9291ILC008064, VSC002048ENTZZZZZ84
1008AX11993ILC011656, VNC003084ENTZZZZZ85
1009AX7164ILC004462, VSC001030ENTZZZZZ86
1009AX7609ILC005267, VSC001079ENTZZZZZ86

Is there a formula or macro that I can use to produce the increasing number sequence in column A each time the value in column D changes?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Assuming you have a header in column A

Book1
ABCD
1Header1Header2Header3Header4
21001AX11172ILC010377, VSC003830ENT002281
31001AX8170ILC006062, VNC001394ENT002281
41001AX8171ILC006386, VNC001395ENT002281
51001AX8263ILC006275ENT002281
61001AX8271ILC006299ENT002281
71001AX8301ILC006362ENT002281
81001AX9933ILC008840, VSC002400ENT002281
91001AX9935ILC008842, VSC002402ENT002281
101002AX8405ILC006620, VSC002028ENT002936
111002AX9255ILC008021, VSC002197ENT002936
121002AX9608ILC008458ENT002936
131003AX9267ILC008037, VSC002043ENT002955
141003AX9289ILC008061ENT002955
151003AX9310ILC008085, VSC002066ENT002955
161003AX9317ILC008093, VSC002067ENT002955
171003AX9385ILC008191ENT002955
181004AX10635ILC009664, VNC002680ENT002957
191004AX9270ILC008040, VEC001321, VSC002045ENT002957
201004AX9315ILC008090, VEC001106, VSC003158ENT002957
211004AX9528ILC008371, VNC002175, VSC002181ENT002957
221005AX9523ILC008363, VEC001036, VNC002463ENT003254
231006AX12113ILC011830, VSC003785ENT003550
241007AX10294ILC009258ENTZZZZZ84
251007AX9291ILC008064, VSC002048ENTZZZZZ84
261008AX11993ILC011656, VNC003084ENTZZZZZ85
271009AX7164ILC004462, VSC001030ENTZZZZZ86
281009AX7609ILC005267, VSC001079ENTZZZZZ86
Sheet1
Cell Formulas
RangeFormula
A2:A28A2=IF(D2<>D1,IF(ROW(A1)=1,1001,A1+1),A1)
 
Upvote 1
Solution
Assuming you have a header in column A

Book1
ABCD
1Header1Header2Header3Header4
21001AX11172ILC010377, VSC003830ENT002281
31001AX8170ILC006062, VNC001394ENT002281
41001AX8171ILC006386, VNC001395ENT002281
51001AX8263ILC006275ENT002281
61001AX8271ILC006299ENT002281
71001AX8301ILC006362ENT002281
81001AX9933ILC008840, VSC002400ENT002281
91001AX9935ILC008842, VSC002402ENT002281
101002AX8405ILC006620, VSC002028ENT002936
111002AX9255ILC008021, VSC002197ENT002936
121002AX9608ILC008458ENT002936
131003AX9267ILC008037, VSC002043ENT002955
141003AX9289ILC008061ENT002955
151003AX9310ILC008085, VSC002066ENT002955
161003AX9317ILC008093, VSC002067ENT002955
171003AX9385ILC008191ENT002955
181004AX10635ILC009664, VNC002680ENT002957
191004AX9270ILC008040, VEC001321, VSC002045ENT002957
201004AX9315ILC008090, VEC001106, VSC003158ENT002957
211004AX9528ILC008371, VNC002175, VSC002181ENT002957
221005AX9523ILC008363, VEC001036, VNC002463ENT003254
231006AX12113ILC011830, VSC003785ENT003550
241007AX10294ILC009258ENTZZZZZ84
251007AX9291ILC008064, VSC002048ENTZZZZZ84
261008AX11993ILC011656, VNC003084ENTZZZZZ85
271009AX7164ILC004462, VSC001030ENTZZZZZ86
281009AX7609ILC005267, VSC001079ENTZZZZZ86
Sheet1
Cell Formulas
RangeFormula
A2:A28A2=IF(D2<>D1,IF(ROW(A1)=1,1001,A1+1),A1)
Perfect, thank you for your help :)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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