Excel 2010 - Convert data

[Brianfree]

Hi, i have an excel spreadsheet with data that looks like below...

REF, VALUE, QTY
0414701006, 0414701053/084, 2
0414720088, 0414720018/029/038, 80

i need to be able to seperate the values to new rows but preserve the Ref and Qty. In addition to this, if there are /xxx numbers i need to replace the last x digits with the alternate value on each row.. I hope this makes sense.

Ideally the output data will need to look like this

REF, VALUE, QTY
0414701006, 0414701053, 2
0414701006, 0414701084, 2
0414720088, 0414720018, 80
0414720088, 0414720029, 80
0414720088, 0414720038, 80

Can anyone help?

thankyou

Brian

 

[SkipVought]

Brien,

Are you saying that you have those 3 rows of data in one column in your spreadsheet?

Have you tried parsing your data via Data> Text to columns... using a COMMA delimiter AND specifying columns 1 & 2 as TEXT.

What have you tried so far? Please post your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Brianfree]

Hi, thanks for your response. I have multiple documents with 10,000s rows of data...

Ive not yet started anything yet as i dont know how to create a vba program sorry

Many thanks

Brian

 

[Andrzejek]

Do I get your request correctly?

Your VALUEs[pre]
0414701[BLUE]053[/BLUE]/[BLUE]084[/BLUE]
0414720[BLUE]018[/BLUE]/[BLUE]029[/BLUE]/[BLUE]038[/BLUE][/pre]

you want to display this way:

[pre]
A B C
REF VALUE QTY
0414701006 0414701[BLUE]053[/BLUE] 2
0414701006 0414701[BLUE]084[/BLUE] 2
0414720088 0414720[BLUE]018[/BLUE] 80
0414720088 0414720[BLUE]029[/BLUE] 80
0414720088 0414720[BLUE]038[/BLUE] 80
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Brianfree Programmer,

I asked a question regarding the data structure of your data. You failed to respond AGAIN.

I say "AGAIN" because this seems to be a habit with you in this forum, going back more than a decade.

Back then I wrote

Brian, since you're a programmer, I figure you must have the chops for logic and math. I would not want to insult a fellow programmer by providing stuff you would have learned in a 101 course.

You never responded.

We'll give you Tips so you can program you solution.

First tip is, parse your data as advised using the COMMA Delimiter in Data > Text to columns, a great data feature of native Excel.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

I may have 'jumped the gun' with my previous post. Sorry...

If you have "an excel spreadsheet with data that looks like below..."

[pre]
A B C ...
REF, VALUE, QTY
0414701006, 0414701053/084, 2
0414720088, 0414720018/029/038, 80
[/pre]
Skip's suggestion with Data > Text to columns would be the first step to do.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson