# List of codes dinamically created



## Samuel Otaviano (Dec 21, 2022)

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?


----------



## Flashbond (Dec 22, 2022)

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 (Dec 22, 2022)

Thanks for your help.

Should i put this formula on B5 cell?

Cause i put it there and got an error.


----------



## Peter_SSs (Dec 22, 2022)

@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 (Dec 22, 2022)

Peter_SSs said:


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


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


----------



## Peter_SSs (Dec 22, 2022)

Samuel Otaviano said:


> 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. 






Samuel Otaviano said:


> 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.xlsmAB1CodeType2website3graphic design4exhibition5graphic design, exhibition6website7website, graphic designSheet1

.. and after:

Samuel Otaviano.xlsmAB1CodeType2WEB_001website3DSG_001graphic design4EXB_001exhibition5DSG_002, EXB_002graphic design, exhibition6WEB_002website7WEB_003, DSG_003website, graphic designSheet1


----------



## Samuel Otaviano (Dec 23, 2022)

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 (Dec 23, 2022)

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


----------

