| View previous topic :: View next topic |
| Author |
Message |
sandyirwan
Newbie
Joined: 03 Jan 2011
Posts: 3
|
 Posted: Mon Jan 03, 2011 10:30 pm Post subject: [SOLVED] split cell to multiple row |
 |
|
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 |
|
 |
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Tue Jan 04, 2011 12:37 am Post subject: |
|
|
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.
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
sandyirwan
Newbie
Joined: 03 Jan 2011
Posts: 3
|
| Posted: Tue Jan 04, 2011 6:37 pm Post subject: split cell to multiple row |
|
|
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 |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Tue Jan 04, 2011 11:14 pm Post subject: |
|
|
| 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
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Wed Jan 05, 2011 2:10 am Post subject: |
|
|
Actually I think that should be:
| Code: | | =VLOOKUP(".+";B1:$B$500;1;0) |
Not really too sure why the .* worked.
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
sandyirwan
Newbie
Joined: 03 Jan 2011
Posts: 3
|
| Posted: Wed Jan 05, 2011 6:11 pm Post subject: |
|
|
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 |
|
|
|
