A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
Like this how to get sequence based on besides column
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Sequence
0
Discussion
Hi Purushuttam and welcome to the Forum
Which version of Excel are you using? (Your profile doesn't include that detail). What's in tbe "besides" column and how long a sequence do you need?
Which version of Excel are you using? (Your profile doesn't include that detail). What's in tbe "besides" column and how long a sequence do you need?
John_Ru (rep: 6152) Jun 13, '23 at 6:18 am
Office 365
As long as beside column has data
As long as beside column has data
Purushuttam Jun 13, '23 at 7:37 am
Answers
0
Purushuttam
A non-VBA way to do that is to set a seed value"A 1" in cell B2 (green cell in the attached file) then this formula in B3 (yellow) and copy down as far as you want:
=CHAR(IF(MOD(ROW(),3)=2,UNICODE(LEFT(B2,1))+1,UNICODE(LEFT(B2,1)))) & " " & CHOOSE(MOD(ROW(),3)+1,2,3,1)where the numbers in bold will need changing if you start that formula at a row which isn't 3, 6, 9 etc.
In column F, a simple If statement is wrapped around that so the values stop after a (contiguous) run of non-zero values in column E. The formula in F3 is:
=IF(E3<>"",CHAR(IF(MOD(ROW(),3)=2,UNICODE(LEFT(F2,1))+1,UNICODE(LEFT(F2,1)))) & " " & CHOOSE(MOD(ROW(),3)+1,2,3,1),"")and I copied the formula down to row 4.
Hope this helps. If so, please remember to mark this Answer as Selected.