Formula to append letters in alphabetical order to unique ID

[Tashat]

Hi All

I have a spreadsheet with Activity IDs and with each activity there could be 1 or more associated Actions. I would like a formula to generate in column B which generates Unique Action IDs based on the Activity ID, as shown below. So that the first action for a given ID ends in "a", the second in "b" and onwards through the alphabet then to "aa", "ab", "ac" etc. Can someone help me with a formula for B2:B10 (onwards) please?

Book3
	A	B
1	Activity ID	Action ID
2	ACT000001	ACT000001a
3	ACT000002	ACT000002a
4	ACT000003	ACT000003a
5	ACT000001	ACT000001b
6	ACT000049	ACT000049a
7	ACT000021	ACT000021a
8	ACT000021	ACT000021b
9	ACT000021	ACT000021c
10	ACT000021	ACT000021d
Sheet1

 

[jasonb75]

This is probably the easiest way to do it.

Book1
	A	B
1	Activity ID	Action ID
2	ACT000001	ACT000001a
3	ACT000002	ACT000002a
4	ACT000003	ACT000003a
5	ACT000001	ACT000001b
6	ACT000049	ACT000049a
7	ACT000021	ACT000021a
8	ACT000021	ACT000021b
9	ACT000021	ACT000021c
10	ACT000021	ACT000021d
Sheet3

Cell Formulas
Range	Formula
B2:B10	B2	=A2&LOWER(SUBSTITUTE(ADDRESS(1,COUNTIF(A$2:A2,A2),4),1,""))

 

[Fluff]

How about

=A2&SUBSTITUTE(ADDRESS(1,COUNTIFS(A$2:A2,A2),4),1,"")

 

[Tashat]

This is probably the easiest way to do it.

Book1
	A	B
1	Activity ID	Action ID
2	ACT000001	ACT000001a
3	ACT000002	ACT000002a
4	ACT000003	ACT000003a
5	ACT000001	ACT000001b
6	ACT000049	ACT000049a
7	ACT000021	ACT000021a
8	ACT000021	ACT000021b
9	ACT000021	ACT000021c
10	ACT000021	ACT000021d
Sheet3

Cell Formulas
Range	Formula
B2:B10	B2	=A2&LOWER(SUBSTITUTE(ADDRESS(1,COUNTIF(A$2:A2,A2),4),1,""))

Amazing Thank you!

 

[Tashat]

How about

=A2&SUBSTITUTE(ADDRESS(1,COUNTIFS(A$2:A2,A2),4),1,"")

Thank you so much as always! This works perfectly. Seems to be the same as JasonB75

 

[Fluff]

Seems to be the same as JasonB75

It is, (although his will return lower case letters & mine will return caps) he was just quicker than me.

Glad we could help & thanks for the feedback.

 

[Tashat]

I have one more question if it's appropriate to ask in this thread. Based on the IDs above, I have another sheet where the user can select the Activity Number in a data validation dropdown list - e.g. ACT000001 and then can select the linked Action Number in the next column. Is there a way of shrinking the drop-down list in the action number column to only present the user with the possible Action Numbers linked with that Activity ID, so the data validation list in the second column is dependent on what the user enters in the first column? e.g.

If the user selects ACT00001 from the drop down list, then the next dropdown list only gives them the choice of ACT000001a or ACT000001b

 

[Fluff]

As that's a totally different question, it needs a new thread. Thanks

 

[Tashat]

As that's a totally different question, it needs a new thread. Thanks

Ah - yes I understand. Will ask separately. Thank you again.