Rearranging text data.

[Houston123]

I currently receive a file that has a list of client ID and each Client ID
contants a list of codes listed on the same row of the Client ID. I need to
have the codes modified so that they show up under a column instead on the
same row.


EX: the data comes to me in this format
Column
A D E
F G
JACK AA AB AC AD
JOEY AA AG AC AL
JAMES AD CD ED DE

I need the data to come out in this format:

JACK AA
JACK AB
JACK AC
JACK AD
JOEY AA
JOEY AG
JOEY AC
JOEY AL


Etc.

 

[Gary''s Student]

This small macro assumes the source data is on Sheet1 and the reorganized
data will be placed on Sheet2:

Sub ReOrganizer()
Dim s1 As Worksheet, s2 As Worksheet
Dim nRows As Long, nCols As Long, k As Long
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
nRows = Cells(Rows.Count, "A").End(xlUp).Row
k = 1
For i = 1 To nRows
v1 = Cells(i, 1).Value
nCols = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 2 To nCols
v2 = Cells(i, j).Value
s2.Cells(k, 1).Value = v1
s2.Cells(k, 2).Value = v2
k = k + 1
Next
Next
End Sub

 

[Houston123]

I'm currently getting Compile Error / Syntax Error.
v1 = Cells(i, 1) .Value

Workbook is named "Worksheet"
Worksheet 1 is "Sheet1"
Worksheet 2 is "Sheet2"

Client ID Data located on Column A, Row 1,2,3,4 ETC.
Codes data located on Column B, C, D, E, F ETC.

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Don Guillett]

OP sent file which was NOT as advertised. It was using cols e:?? and
formulas to get the split. So, just easier to do TTC and then line em up.
This will do no matter how many codes.

Option Explicit
Sub DoTextToColumns() 'SalesAidSoftware
Application.DisplayAlerts = False
Cells(2, 3).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1). _
TextToColumns Destination:=Range("E2"), _
DataType:=xlDelimited,Comma:=True
Application.DisplayAlerts = True
End Sub

Sub ReOrganizerDon() 'SalesAidSoftware
DoTextToColumns

Dim ss, do As Worksheet
Dim i, lc, i, dlr As Long
Set ss = Sheets("Current Data")
Set ds = Sheets("Processed Data")
ds.Columns(1).Resize(, 2).Clear

For i = 2 To ss.Cells(Rows.Count, 1).End(xlUp).Row

lc = ss.Cells(i, Columns.Count).End(xlToLeft).Column
For j = 5 To lc
dlr = ds.Cells(Rows.Count, 1).End(xlUp).Row + 1
ds.Cells(dlr, 1) = ss.Cells(i, 1)
ds.Cells(dlr, 2) = ss.Cells(i, j)
Next j

Next i
End Sub