Process to Match IDs to Multiple Cells Based off of Another Cell?

[CtrlAltRage]

Hey all,

I'm not entirely sure if this is a VBA question or a macro or formula I can use. But here's what I'm trying to do.

I have two files, we'll call them File 1 and File 2.

In File One, there are 3 Columns:

ID, Name, Title IDs

ID and Name have one entry in their respective cells. Title IDs may have multiple IDs separated by commas (0013,0014,0015, etc)

Example:

ID	Name	Title IDs
01	John Smith	0012,0034,0090
02	Jane Doe	0012,0078,0090,0100

In File Two, there are two columns:

Title ID, Title Name

Each column in File Two have one Entry.

Example:

Title ID	Title Name
0012	Name 1
0034	Name 2
0078	Name 3
0090	Name 4
0100	Name 5

What I'm trying to do is take those two files, and combine them into one new file where it populates the IDs from File One and matches them to the respective Title IDs of File Two in individual cells.

Example:

ID	Title ID
01	0012
02	0012
01	0034
02	0078
01	0090
02	0090
02	0100

I hope what I'm trying to convey is making sense - right now splitting the Title IDs in File One is not an option, so ideally I need to create a new file for it.

Any help and insight would be appreciated, thank you!

 

[Peter_SSs]

I'm unsure what File Two has to do with it? From what you describe that final result is obtainable from File One only.
For example, if you have the required functions, isn't this doing what you asked?

22 12 24.xlsm
	A	B	C	D	E	F
1	ID	Name	Title IDs		ID	Title ID
2	01	John Smith	0012,0034,0090		01	0012
3	02	Jane Doe	0012,0078,0090,0100		02	0012
4					01	0034
5					02	0078
6					01	0090
7					02	0090
8					02	0100
9
Split IDs

Cell Formulas
Range	Formula
E2:F8	E2	=SORT(WRAPROWS(TEXTSPLIT(TEXTJOIN(",",,BYROW(A2:C3,LAMBDA(a,INDEX(a,1)&","&SUBSTITUTE(INDEX(a,3),",",","&INDEX(a,1)&",")))),","),2),2)
Dynamic array formulas.

 

[shinigamilight]

Sub string_er()
       Dim store() As String
       Dim k, j As Integer
       Dim lr As Long
       Dim wk1, wk2 As Worksheet
       Dim number As String
       Set wk1 = Sheets("sheet23") 'input sheet name
       Set wk2 = Sheets("sheet24") ' ouput sheet name
       lr = wk1.Cells(Rows.Count, 1).End(xlUp).Row
        For k = 2 To lr
            store = Split(wk1.Range("C" & k), ",")
                number = wk1.Range("A" & k)
                For j = 0 To UBound(store)
                        wk2.Range("B:B").NumberFormat = "@"
                        wk2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = store(j)
                        wk2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = number
                Next j
        Next k
End Sub