List of codes dinamically created

[Samuel Otaviano]

Hi,





I'd like to create a list with numbered codes composed by an acronym related to the type of project wich is in another column. Just as the example in the attached image.

As the example in the image shows, it's possible to have more than one type of ptoject in the same cell, in those cases the code cell also has more than one code.

Any suggestion?

 

[Legacy 143009]

B5:

=TEXTJOIN(", ",1,IF($A$1:$A$3=LEFT(TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(A5,",","</y><y>")&"</y></x>","//y")),3),$B$1:$B$3,""))

This is an array formula. Hitting only Enter is not enough. You must press Ctrl+Shift+Enter together after paste.

 

[Samuel Otaviano]

Thanks for your help.

Should i put this formula on B5 cell?

Cause i put it there and got an error.

 

[Peter_SSs]

@Flashbond
The OP's profile shows Excel 2013 so they would not have TEXTJOIN.

@Samuel Otaviano
Welcome to the MrExcel board!
If Excel 2013 is correct then a formula solution for this sort of thing is not very practical.
Would a macro solution be acceptable?

 

[Samuel Otaviano]

@Flashbond
The OP's profile shows Excel 2013 so they would not have TEXTJOIN.

@Samuel Otaviano
Welcome to the MrExcel board!
If Excel 2013 is correct then a formula solution for this sort of thing is not very practical.
Would a macro solution be acceptable?

Yes sure. If a macro can solve it, that's ok. I forgot to mention that i'm using Excel 2013.

 

[Peter_SSs]

I forgot to mention that i'm using Excel 2013.

You didn't need to mention that as it is shown at the left of each of your posts.

If a macro can solve it, that's ok.

So, give this one a try.

Sub Create_Codes()
  Dim d As Object
  Dim a As Variant, t As Variant
  Dim sCode As String
  Dim i As Long, j As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("B2", Range("B" & Rows.Count).End(xlUp))
  For i = 1 To UBound(a)
    t = Split(a(i, 1), ", ")
    For j = 0 To UBound(t)
      Select Case LCase(t(j))
        Case "website": sCode = "WEB"
        Case "graphic design": sCode = "DSG"
        Case "exhibition": sCode = "EXB"
        Case Else: sCode = "ZZZ"
      End Select
      d(sCode) = d(sCode) + 1
      t(j) = sCode & Format(d(sCode), "_000")
    Next j
    a(i, 1) = Join(t, ", ")
    Range("A2").Resize(UBound(a)).Value = a
  Next i
End Sub

My sheet before the code:

Samuel Otaviano.xlsm
	A	B
1	Code	Type
2		website
3		graphic design
4		exhibition
5		graphic design, exhibition
6		website
7		website, graphic design
Sheet1

.. and after:

Samuel Otaviano.xlsm
	A	B
1	Code	Type
2	WEB_001	website
3	DSG_001	graphic design
4	EXB_001	exhibition
5	DSG_002, EXB_002	graphic design, exhibition
6	WEB_002	website
7	WEB_003, DSG_003	website, graphic design
Sheet1

 

[Samuel Otaviano]

It worked perfectly.

I'm not familiar with VBA but i know a bit of javascript, so i think i could understand the basic structure of this code. It's a good reference to begin to study VBA.

Thanks a lot!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.