OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] split cell to multiple row

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
sandyirwan
Newbie
Newbie


Joined: 03 Jan 2011
Posts: 3

PostPosted: Mon Jan 03, 2011 10:30 pm    Post subject: [SOLVED] split cell to multiple row Reply with quote

Hi All,

I'm currently having a problem of converting my data. Would appreciate if you can advise me on this.

I have a file with the following format:

type description price
100 harmony 1.25
201,203 guitar 3.2
2,45,893 stylus 0.25

How do i convert to be such as:

type description price
100 harmony 1.25
201 guitar 3.2
203 guitar 3.2
2 stylus 0.25
45 stylus 0.25
893 stylus 0.25

Please advise.
Thanks.


Last edited by sandyirwan on Mon Jan 10, 2011 8:33 pm; edited 1 time in total
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Tue Jan 04, 2011 12:37 am    Post subject: Reply with quote

If you do not have too many different numbers for each item you could open in Writer or a text editor and replace commas with paragraphs (use AltSearch in Writer) so you get:

100 harmony 1.25
201
203 guitar 3.2
2
45
893 stylus 0.25

copy and paste special (Ctrl+Shift+V) into Calc selecting "Unformatted text" and then a space as a separator (deselect tab).

In D1 insert

Code:
=IF(B1="";IF(B2="";B3;B2);B1)

and fill down. Select and copy column D to the clipboard (Ctrl+C) and then paste special (Ctrl+Shift+V) onto column B with only "Text" selected under Selection.
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
sandyirwan
Newbie
Newbie


Joined: 03 Jan 2011
Posts: 3

PostPosted: Tue Jan 04, 2011 6:37 pm    Post subject: split cell to multiple row Reply with quote

Hi Robert,

thank you very much for the advise. I've tried it out & it seems ok.

I think will need to tweek the formula as I've some of the cells under A1 have lots of value with comma deliminated.

e.g. 23,49,54.29,689,3948, 374,204,029,34 mobile 3.56
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Tue Jan 04, 2011 11:14 pm    Post subject: Reply with quote

Code:
=IF(B1="";IF(B2="";IF(B3="";IF(B4="";IF(B5="";IF(B6="";IF(B7="";IF(B8="";IF(B9="";IF(B10="";B11;B10);B9);B8);B7);B6);B5);B4);B3);B2);B1)

does work, but you need to determine how many IF statements you need from the maximum number of different numbers you have for each item type.

Code:
=VLOOKUP(".*";B1:$B$500;1;0)

works if you have "Enable regular expressions in formulas" checked at Tools>Options...>OpenOffice.org Calc>Calculate
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Wed Jan 05, 2011 2:10 am    Post subject: Reply with quote

Actually I think that should be:

Code:
=VLOOKUP(".+";B1:$B$500;1;0)

Not really too sure why the .* worked.
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
sandyirwan
Newbie
Newbie


Joined: 03 Jan 2011
Posts: 3

PostPosted: Wed Jan 05, 2011 6:11 pm    Post subject: Reply with quote

I've modified the formula and its working

Code:
=IF(B3="";IF(B2="";D1;B2);B3)


The first few lines can just manual copy while the rest will auto generate.

Thanks, Robert.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group