cjjn
02-20-2013, 10:29 PM
Hi Rick
http://www.excelfox.com/forum/f22/redistribute-delimited-column-data-into-separate-rows-keeping-other-data-420/ is great - I need to do exactly this except with multiple columns
for example looks like:
Meals, Accom, Labour 100,200,300 200,400,600 500,700,100
needs to be:
Meals 100 200 500
Accom 200 400 700
Labour 300 600 100
In my actual spreadsheet more than 3 columns need to be split, columns a-->s are single line and then columns T--> AF all need to be split
Any help you can provide would be greatly appreciated - I am pretty good in Excel but not at all good with VBA.
Thank you in advance.
C
The following scenario seems to come up somewhat often at the various forums I participate in... take a table of data where one column contains delimited data and split that delimited data so that each item is on a row of its own, copying the associated data into the blank cells created by the split. Visually, we want to go from this table...
A
B
C
D
1
Name
Client
Number
Parts
Ordered
2
Rick
1111111
P1, P2, P3
3
Sam
2222222
P2, P5
4
Joe
3333333
P3
5
Bill
4444444
P4, P6, P7, P8
6
to this one
A
B
C
D
1
Name
Client
Number
Parts
Ordered
2
Rick
1111111
P1
3
Rick
1111111
P2
4
Rick
1111111
P3
5
Sam
2222222
P2
6
Sam
2222222
P5
7
Joe
3333333
P3
8
Bill
4444444
P4
9
Bill
4444444
P6
10
Bill
4444444
P7
11
Bill
4444444
P8
12
Below is a macro that will accomplish this task. Note though that I have generalize it somewhat. Usually in the requests the delimited data is in the last column as shown above, however, there is no need for this to be the case... this macro will allow any column to be the delimited column.
Sub RedistributeData()
Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
Const Delimiter As String = ", "
Const DelimitedColumn As String = "C"
Const TableColumns As String = "A:C"
Const StartRow As Long = 2
Application.ScreenUpdating = False
LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
For X = LastRow To StartRow Step -1
Data = Split(Cells(X, DelimitedColumn), Delimiter)
If UBound(Data) Then
Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert xlShiftDown
End If
Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
Next
LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
On Error GoTo NoBlanks
Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
On Error GoTo 0
For Each A In Table.SpecialCells(xlBlanks).Areas
A.FormulaR1C1 = "=R[-1]C"
A.Value = A.Value
Next
NoBlanks:
Application.ScreenUpdating = True
End Sub
There are four constants (the Const statements) that you need to match to your actual worksheet conditions in order to work. The first is named Delimiter and it can be one or more characters long. The second is named DelimitedColumn and specifies the column letter containing the delimited cells. The third is named TableColumns and it specifies the columns occupied by your data (which must always include the column with the delimited cells. The last one is named StartRow and it specifies the row containing the first piece of data (that is, it is the row number below the headers, if any).
http://www.excelfox.com/forum/f22/redistribute-delimited-column-data-into-separate-rows-keeping-other-data-420/ is great - I need to do exactly this except with multiple columns
for example looks like:
Meals, Accom, Labour 100,200,300 200,400,600 500,700,100
needs to be:
Meals 100 200 500
Accom 200 400 700
Labour 300 600 100
In my actual spreadsheet more than 3 columns need to be split, columns a-->s are single line and then columns T--> AF all need to be split
Any help you can provide would be greatly appreciated - I am pretty good in Excel but not at all good with VBA.
Thank you in advance.
C
The following scenario seems to come up somewhat often at the various forums I participate in... take a table of data where one column contains delimited data and split that delimited data so that each item is on a row of its own, copying the associated data into the blank cells created by the split. Visually, we want to go from this table...
A
B
C
D
1
Name
Client
Number
Parts
Ordered
2
Rick
1111111
P1, P2, P3
3
Sam
2222222
P2, P5
4
Joe
3333333
P3
5
Bill
4444444
P4, P6, P7, P8
6
to this one
A
B
C
D
1
Name
Client
Number
Parts
Ordered
2
Rick
1111111
P1
3
Rick
1111111
P2
4
Rick
1111111
P3
5
Sam
2222222
P2
6
Sam
2222222
P5
7
Joe
3333333
P3
8
Bill
4444444
P4
9
Bill
4444444
P6
10
Bill
4444444
P7
11
Bill
4444444
P8
12
Below is a macro that will accomplish this task. Note though that I have generalize it somewhat. Usually in the requests the delimited data is in the last column as shown above, however, there is no need for this to be the case... this macro will allow any column to be the delimited column.
Sub RedistributeData()
Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
Const Delimiter As String = ", "
Const DelimitedColumn As String = "C"
Const TableColumns As String = "A:C"
Const StartRow As Long = 2
Application.ScreenUpdating = False
LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
For X = LastRow To StartRow Step -1
Data = Split(Cells(X, DelimitedColumn), Delimiter)
If UBound(Data) Then
Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert xlShiftDown
End If
Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
Next
LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
On Error GoTo NoBlanks
Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
On Error GoTo 0
For Each A In Table.SpecialCells(xlBlanks).Areas
A.FormulaR1C1 = "=R[-1]C"
A.Value = A.Value
Next
NoBlanks:
Application.ScreenUpdating = True
End Sub
There are four constants (the Const statements) that you need to match to your actual worksheet conditions in order to work. The first is named Delimiter and it can be one or more characters long. The second is named DelimitedColumn and specifies the column letter containing the delimited cells. The third is named TableColumns and it specifies the columns occupied by your data (which must always include the column with the delimited cells. The last one is named StartRow and it specifies the row containing the first piece of data (that is, it is the row number below the headers, if any).