Hi all,

I have data that looks something like this

6"/7"/3" DATA1
1" DATA2
2"/1" DATA3
8" DATA4
9" DATA5
8" DATA6
1"/2" DATA7

what i need to be able to do is to split the data so that when there is
more than one size in one cell the whole row is copied as many times as
there are sizes so the result would look like. The copying of the data
is not a problem but what code do I use to split it all up.

6" DATA1
7" DATA1
3" DATA1
1" DATA2
2" DATA3
1" DATA3
8" DATA4
9" DATA5
8" DATA6
1" DATA7
2" DATA7

Hope this makes sense.

Thanks in advance


--
JamesBurrows
------------------------------------------------------------------------
JamesBurrows's Profile: http://www.excelforum.com/member.php...o&userid=35101
View this thread: http://www.excelforum.com/showthread...hreadid=551316

I have just learned i need to do something to do with delimiters and
have the following code

Instr (1,Cells(rowNum, colNum).Value,"/"

but what i dont know now is how to take the information from before and
after the / and what if it has 3/s (ie 8"/7"/2")


--
JamesBurrows
------------------------------------------------------------------------
JamesBurrows's Profile: http://www.excelforum.com/member.php...o&userid=35101
View this thread: http://www.excelforum.com/showthread...hreadid=551316

James,

Try this: change w/sheet names as required.

Sub splitData()
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
rr = 2
For r = 2 To lastrow '<==== Assumes data starts on row 2
i = 1
Do
j = InStr(i, .Cells(r, "A"), "/")
If j < 0 Then
n = j - i
Worksheets("Sheet2").Cells(rr, "A") = Mid(.Cells(r, "A"), i, n)
Worksheets("Sheet2").Cells(rr, "B") = .Cells(r, "B")
i = j + 1
Else
Worksheets("Sheet2").Cells(rr, "A") = Mid(.Cells(r, "A"), i, 255)
Worksheets("Sheet2").Cells(rr, "B") = .Cells(r, "B")
End If
rr = rr + 1
Loop Until j = 0
Next r
End With
End Sub

HTH
"JamesBurrows" wrote:


I have just learned i need to do something to do with delimiters and
have the following code

Instr (1,Cells(rowNum, colNum).Value,"/"

but what i dont know now is how to take the information from before and
after the / and what if it has 3/s (ie 8"/7"/2")


--
JamesBurrows
------------------------------------------------------------------------
JamesBurrows's Profile: http://www.excelforum.com/member.php...o&userid=35101
View this thread: http://www.excelforum.com/showthread...hreadid=551316

Thanks very much for that it does exavtly what i needed it too, all ive
got to do is work out how to change the variables and worksheets to
what i need but otherwise, spot on, thanks.


--
JamesBurrows
------------------------------------------------------------------------
JamesBurrows's Profile: http://www.excelforum.com/member.php...o&userid=35101
View this thread: http://www.excelforum.com/showthread...hreadid=551316

I have tested the code and am begginning to understand it, but am really
new to programming and am not sure how to intergrate it into my code as
a function that is called in my code or taking individual lines and
placing them intop my code, sorry Im sure im being really dopey but if
I can get this sorted then thats my job on this finished, would be very
grateful of any help, see file attached. Thanks AGAIN!


+-------------------------------------------------------------------+
|Filename: CSV Test File.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4877 |
+-------------------------------------------------------------------+

--
JamesBurrows
------------------------------------------------------------------------
JamesBurrows's Profile: http://www.excelforum.com/member.php...o&userid=35101
View this thread: http://www.excelforum.com/showthread...hreadid=551316

Which worksheet do you want the results in? and which column(s) consistitutes
"Data1" etc. If you can post an example it would help.

May be some delay before next reply!

"JamesBurrows" wrote:


I have tested the code and am begginning to understand it, but am really
new to programming and am not sure how to intergrate it into my code as
a function that is called in my code or taking individual lines and
placing them intop my code, sorry Im sure im being really dopey but if
I can get this sorted then thats my job on this finished, would be very
grateful of any help, see file attached. Thanks AGAIN!


+-------------------------------------------------------------------+
|Filename: CSV Test File.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4877 |
+-------------------------------------------------------------------+

--
JamesBurrows
------------------------------------------------------------------------
JamesBurrows's Profile: http://www.excelforum.com/member.php...o&userid=35101
View this thread: http://www.excelforum.com/showthread...hreadid=551316

Ok im getting there now but am still struggling, I have attached my code
and hope this helps show where im trying to go.


+-------------------------------------------------------------------+
|Filename: Code.txt |
|Download: http://www.excelforum.com/attachment.php?postid=4880 |
+-------------------------------------------------------------------+

--
JamesBurrows
------------------------------------------------------------------------
JamesBurrows's Profile: http://www.excelforum.com/member.php...o&userid=35101
View this thread: http://www.excelforum.com/showthread...hreadid=551316

In answer to your earlier question:

The data should be read from Line List Sheet and copied to Tags CSV.

The sizes will be read from Line List Sheet (Colum A) and copied to
Tags CSV (Colum B). And everytime a Size is copied across the whole row
should populate with information, wether that be a size, as in one size
or 1of three sizes. I am sorry if my explaination is a bit poor but
hopefully you get my drift.


--
JamesBurrows
------------------------------------------------------------------------
JamesBurrows's Profile: http://www.excelforum.com/member.php...o&userid=35101
View this thread: http://www.excelforum.com/showthread...hreadid=551316

James,
Attached is my attempt to insert code.
I limited the testing to the first nine rows of data AND I commented
out a statement in FILLFORM which said "Update" error as this was
overriding the pipe size.

I note you had other blocks of data with headings further down the list
- why are the headings there rather than a single set at the top?

You can contact me direct (to avoid long discussions on the NGs) at
toppers<atnospam.john.topley.fsnet.co.uk

Remove the nospam from the above.

John


+-------------------------------------------------------------------+
|Filename: CSV Test File X.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4881 |
+-------------------------------------------------------------------+

--
Toppers
------------------------------------------------------------------------
Toppers's Profile: http://www.excelforum.com/member.php...o&userid=30076
View this thread: http://www.excelforum.com/showthread...hreadid=551316

typo ... should be johntopley

"Toppers" wrote:


James,
Attached is my attempt to insert code.
I limited the testing to the first nine rows of data AND I commented
out a statement in FILLFORM which said "Update" error as this was
overriding the pipe size.

I note you had other blocks of data with headings further down the list
- why are the headings there rather than a single set at the top?

You can contact me direct (to avoid long discussions on the NGs) at
toppers<atnospam.john.topley.fsnet.co.uk

Remove the nospam from the above.

John


+-------------------------------------------------------------------+
|Filename: CSV Test File X.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4881 |
+-------------------------------------------------------------------+

--
Toppers
------------------------------------------------------------------------
Toppers's Profile: http://www.excelforum.com/member.php...o&userid=30076
View this thread: http://www.excelforum.com/showthread...hreadid=551316