Text to Columns to Rows via VBA

[alexaronson]

Hello and thanks for looking at my question.

I have table that has the following headings:
Week, Date, Group, Item Codes
Data example:
01 1/30/2011 The Group Item Codes
The data in Item Codes is stored as such: item#1, item#2, item#3 with each item seperated by "," and " ".

What I need to do is deliminate the cell Item Codes so that item#1, item+2, item#3 are on their own rows with the corresponding information for Week, Date, and Group.

I could do this manually, but I have over 100 records in my table that will blosom for into 1,000 very easily

Any suggestions?

 

[hiker95]

alexaronson,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste

 

[alexaronson]

Hiker95,

My corporate laptop will not allow me to install Excel Jeanie and the other two links do not open up to anything.

 

[alexaronson]

Row 1, 2, and 3 looks like this

A B C r1, r2, r3
A B D r4, r5, r6, r7
A B E r8, r9
Desired output would be to get this on seperate rows

A B C r1
A B C r2
A B C r3
A B D r4
A B D r5
A B D r6
A B D r7
A B D r8
A B D r9

 

[snb_]

sub snb()
  sq=columns(1).specialcells(2)
  for j=1 to ubound(sq)
    sn=split(sq(j,1))
    c01 = c01 & "," & replace(sq(j,1),",","," & sn(0) & " " & sn(1) & " " & sn(2)) 
  next
  sq=filter(split(c01),",")," ")
  cells(1,5).resize(ubound(sq)+1)=application.transpose(sq)
end sub

 

[alexaronson]

snb,

Thanks for the code. When I cut and paste it into Module 1, I get red text at

sq=filter(split(c01),",")," ")

giving me a complie error: syntax error

Any suggestions?

 

[hiker95]

alexaronson,

If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[snb_]

fair enough:

sq=filter(split(c01,",")," ")

 

[mirabeau]

With data as in your post#4, in 4 columns, is it this sort of thing you're looking for?

Sub somecode()
Dim e, x, k As Long, q
For Each e In Range("D2").Resize(Range("D:D")(Rows.Count).End(3).Row - 1)
    x = Rows(e.Row).Resize(, 4)
    For Each q In Split(e, ", ")
        k = k + 1
        x(1, 4) = q
        Range("F" & k + 1).Resize(, 4) = x
    Next q
Next e
End Sub

 

[hiker95]

alexaronson,


Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Week	Date	Group	Item Codes
2	A	B	C	r1, r2, r3
3	A	B	D	r4, r5, r6, r7
4	A	B	E	r8, r9
5
6
7
8
9
10
11
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Week	Date	Group	Item Codes		Week	Date	Group	Item Codes
2	A	B	C	r1, r2, r3		A	B	C	r1
3	A	B	D	r4, r5, r6, r7		A	B	C	r2
4	A	B	E	r8, r9		A	B	C	r3
5						A	B	D	r4
6						A	B	D	r5
7						A	B	D	r6
8						A	B	D	r7
9						A	B	E	r8
10						A	B	E	r9
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 02/18/2011
' http://www.mrexcel.com/forum/showthread.php?t=529963
Dim c As Range, Sp, NR As Long
Application.ScreenUpdating = False
Range("F1:I1").Value = Range("A1:D1").Value
For Each c In Range("D2", Range("D" & Rows.Count).End(xlUp))
  Sp = Split(Trim(c), ", ")
  NR = Range("F" & Rows.Count).End(xlUp).Offset(1).Row
  Range("F" & NR).Resize(UBound(Sp) + 1, 3).Value = c.Offset(, -3).Resize(, 3).Value
  Range("I" & NR).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
Next c
Columns("F:I").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.