Hey, Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it? See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome. Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
@thuo see link http://office.microsoft.com/en-001/excel-help/split-text-into-different-cell... On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Walter Nyamweya
Macro assuming you can write one...:( Regards, Kennedy KK Mobile: +254751110168 Skype: k.kairu Gtalk: kkairu On Wed, Jul 10, 2013 at 5:23 PM, wa <wanangu@gmail.com> wrote:
@thuo see link
http://office.microsoft.com/en-001/excel-help/split-text-into-different-cell...
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Walter Nyamweya
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On 10 July 2013 17:23, wa <wanangu@gmail.com> wrote:
@thuo see link
http://office.microsoft.com/en-001/excel-help/split-text-into-different-cell...
Thanks, but this is text to column. I need text to rows. WIlson
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Walter Nyamweya
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Create a macro in Excel. Add this code to your macro, which will turn your comma-delimited text to rows in Excel. *Sub SplitAndTranspose()* *Dim N() As String* *N = Split(ActiveCell, ", ") // this comma has a space after it, so put yours as row appears* *ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)* *End Sub* * * Once you've created the macro highlight your cell and run the macro. That's it! * * Regards * * On Wed, Jul 10, 2013 at 8:44 PM, Thuo Wilson <lixton@gmail.com> wrote:
On 10 July 2013 17:23, wa <wanangu@gmail.com> wrote:
@thuo see link
http://office.microsoft.com/en-001/excel-help/split-text-into-different-cell...
Thanks, but this is text to column. I need text to rows.
WIlson
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Walter Nyamweya
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Anthony Tai "A good head and a good heart are always a formidable combination."
Easy, treat each column separately copy third column to an editor e.g. crimson editor then replace each comma with a tab and paste back to excel. ./TheMburu On Wed, Jul 10, 2013 at 8:55 PM, Anthony Tai <jicholatai@gmail.com> wrote:
Create a macro in Excel. Add this code to your macro, which will turn your comma-delimited text to rows in Excel.
*Sub SplitAndTranspose()* *Dim N() As String* *N = Split(ActiveCell, ", ") // this comma has a space after it, so put yours as row appears* *ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)* *End Sub* * * Once you've created the macro highlight your cell and run the macro. That's it! * * Regards * *
On Wed, Jul 10, 2013 at 8:44 PM, Thuo Wilson <lixton@gmail.com> wrote:
On 10 July 2013 17:23, wa <wanangu@gmail.com> wrote:
@thuo see link
http://office.microsoft.com/en-001/excel-help/split-text-into-different-cell...
Thanks, but this is text to column. I need text to rows.
WIlson
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Walter Nyamweya
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Anthony Tai
"A good head and a good heart are always a formidable combination."
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Conservatism is the adherence to the old tried against the new untried.
@george, the comma separated column needs to be converted to rows not columns. Only way I am aware of is using a macro as highlighted by Tai Regards, Kennedy KK Mobile: +254751110168 Skype: k.kairu Gtalk: kkairu On Wed, Jul 10, 2013 at 9:50 PM, TheMburu George <themburu@gmail.com> wrote:
Easy, treat each column separately copy third column to an editor e.g. crimson editor then replace each comma with a tab and paste back to excel.
./TheMburu
On Wed, Jul 10, 2013 at 8:55 PM, Anthony Tai <jicholatai@gmail.com> wrote:
Create a macro in Excel. Add this code to your macro, which will turn your comma-delimited text to rows in Excel.
*Sub SplitAndTranspose()* *Dim N() As String* *N = Split(ActiveCell, ", ") // this comma has a space after it, so put yours as row appears* *ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)* *End Sub* * * Once you've created the macro highlight your cell and run the macro. That's it! * * Regards * *
On Wed, Jul 10, 2013 at 8:44 PM, Thuo Wilson <lixton@gmail.com> wrote:
On 10 July 2013 17:23, wa <wanangu@gmail.com> wrote:
@thuo see link
http://office.microsoft.com/en-001/excel-help/split-text-into-different-cell...
Thanks, but this is text to column. I need text to rows.
WIlson
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Walter Nyamweya
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Anthony Tai
"A good head and a good heart are always a formidable combination."
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Conservatism is the adherence to the old tried against the new untried.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Thuo, I deal with these all the time. Send me the file I crack it in a min. David Kandie OpenCastLabs Consulting www.opencast-labs.com Advanced Excel Training in Eastern Africa & DR Congo ------------------------------------------------------- Budgeting, Forecasting & Financial Modeling Training - www.opencastlabs.com On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
This is actually quite simple, just copy that column into a notepad, save as csv, open in excel and voila! copy the values back as individual columns. Hii mambo yote ya Macro is not necessary.... On Wed, Jul 10, 2013 at 10:54 PM, OpenCastLabs Consulting < dkipkulei@gmail.com> wrote:
Thuo, I deal with these all the time. Send me the file I crack it in a min.
David Kandie OpenCastLabs Consulting www.opencast-labs.com Advanced Excel Training in Eastern Africa & DR Congo
------------------------------------------------------- Budgeting, Forecasting & Financial Modeling Training - www.opencastlabs.com
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On 11 July 2013 07:31, Alex Mwega <slylex@gmail.com> wrote:
This is actually quite simple, just copy that column into a notepad, save as csv, open in excel and voila! copy the values back as individual columns. Hii mambo yote ya Macro is not necessary....
inakaa watu wengi hapa walianguka mtihani juu ya kutosikiza ama kutosoma sentensi kwa maakini. Am interested in rows not column - Column is easy. Nitajaribu Tai alivyosema. Wilson.
On Wed, Jul 10, 2013 at 10:54 PM, OpenCastLabs Consulting < dkipkulei@gmail.com> wrote:
Thuo, I deal with these all the time. Send me the file I crack it in a min.
David Kandie OpenCastLabs Consulting www.opencast-labs.com Advanced Excel Training in Eastern Africa & DR Congo
------------------------------------------------------- Budgeting, Forecasting & Financial Modeling Training - www.opencastlabs.com
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Save as either txt or CSV and by making use of " and , use PHP or another good programming language to manipulate into another CSV or txt and convert back to excel :) On 11 July 2013 11:47, Thuo Wilson <lixton@gmail.com> wrote:
On 11 July 2013 07:31, Alex Mwega <slylex@gmail.com> wrote:
This is actually quite simple, just copy that column into a notepad, save as csv, open in excel and voila! copy the values back as individual columns. Hii mambo yote ya Macro is not necessary....
inakaa watu wengi hapa walianguka mtihani juu ya kutosikiza ama kutosoma sentensi kwa maakini. Am interested in rows not column - Column is easy.
Nitajaribu Tai alivyosema.
Wilson.
On Wed, Jul 10, 2013 at 10:54 PM, OpenCastLabs Consulting < dkipkulei@gmail.com> wrote:
Thuo, I deal with these all the time. Send me the file I crack it in a min.
David Kandie OpenCastLabs Consulting www.opencast-labs.com Advanced Excel Training in Eastern Africa & DR Congo
------------------------------------------------------- Budgeting, Forecasting & Financial Modeling Training - www.opencastlabs.com
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Isaac Kiplagat. KIP®
You can try Apache Poi <http://poi.apache.org/>. Regards, Richard Gathogo. On Thu, Jul 11, 2013 at 12:26 PM, Isaac Kiplagat <isaac.kiplagat@gmail.com>wrote:
Save as either txt or CSV and by making use of " and , use PHP or another good programming language to manipulate into another CSV or txt and convert back to excel :)
On 11 July 2013 11:47, Thuo Wilson <lixton@gmail.com> wrote:
On 11 July 2013 07:31, Alex Mwega <slylex@gmail.com> wrote:
This is actually quite simple, just copy that column into a notepad, save as csv, open in excel and voila! copy the values back as individual columns. Hii mambo yote ya Macro is not necessary....
inakaa watu wengi hapa walianguka mtihani juu ya kutosikiza ama kutosoma sentensi kwa maakini. Am interested in rows not column - Column is easy.
Nitajaribu Tai alivyosema.
Wilson.
On Wed, Jul 10, 2013 at 10:54 PM, OpenCastLabs Consulting < dkipkulei@gmail.com> wrote:
Thuo, I deal with these all the time. Send me the file I crack it in a min.
David Kandie OpenCastLabs Consulting www.opencast-labs.com Advanced Excel Training in Eastern Africa & DR Congo
------------------------------------------------------- Budgeting, Forecasting & Financial Modeling Training - www.opencastlabs.com
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Isaac Kiplagat. KIP®
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Well you can use many of the same to archive your goal , whats amazing is that all of us have different ideas. Its like , being taught on how to slice through an orange either vertically or horizontally all in all you still eat it. On Thu, Jul 11, 2013 at 12:45 PM, Richard Gathogo <muraguri2005@gmail.com>wrote:
You can try Apache Poi <http://poi.apache.org/>. Regards,
Richard Gathogo.
On Thu, Jul 11, 2013 at 12:26 PM, Isaac Kiplagat <isaac.kiplagat@gmail.com
wrote:
Save as either txt or CSV and by making use of " and , use PHP or another good programming language to manipulate into another CSV or txt and convert back to excel :)
On 11 July 2013 11:47, Thuo Wilson <lixton@gmail.com> wrote:
On 11 July 2013 07:31, Alex Mwega <slylex@gmail.com> wrote:
This is actually quite simple, just copy that column into a notepad, save as csv, open in excel and voila! copy the values back as individual columns. Hii mambo yote ya Macro is not necessary....
inakaa watu wengi hapa walianguka mtihani juu ya kutosikiza ama kutosoma sentensi kwa maakini. Am interested in rows not column - Column is easy.
Nitajaribu Tai alivyosema.
Wilson.
On Wed, Jul 10, 2013 at 10:54 PM, OpenCastLabs Consulting < dkipkulei@gmail.com> wrote:
Thuo, I deal with these all the time. Send me the file I crack it in a min.
David Kandie OpenCastLabs Consulting www.opencast-labs.com Advanced Excel Training in Eastern Africa & DR Congo
------------------------------------------------------- Budgeting, Forecasting & Financial Modeling Training - www.opencastlabs.com
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Isaac Kiplagat. KIP®
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
And here's another solution on a different slice axis.. I am sure ruby and perl folks can shell out a one liner https://gist.github.com/anonymous/5978401 On Thu, Jul 11, 2013 at 1:53 PM, Benjamin <anangwe@gmail.com> wrote:
Well you can use many of the same to archive your goal , whats amazing is that all of us have different ideas.
Its like , being taught on how to slice through an orange either vertically or horizontally all in all you still eat it.
On Thu, Jul 11, 2013 at 12:45 PM, Richard Gathogo <muraguri2005@gmail.com>wrote:
You can try Apache Poi <http://poi.apache.org/>. Regards,
Richard Gathogo.
On Thu, Jul 11, 2013 at 12:26 PM, Isaac Kiplagat < isaac.kiplagat@gmail.com> wrote:
Save as either txt or CSV and by making use of " and , use PHP or another good programming language to manipulate into another CSV or txt and convert back to excel :)
On 11 July 2013 11:47, Thuo Wilson <lixton@gmail.com> wrote:
On 11 July 2013 07:31, Alex Mwega <slylex@gmail.com> wrote:
This is actually quite simple, just copy that column into a notepad, save as csv, open in excel and voila! copy the values back as individual columns. Hii mambo yote ya Macro is not necessary....
inakaa watu wengi hapa walianguka mtihani juu ya kutosikiza ama kutosoma sentensi kwa maakini. Am interested in rows not column - Column is easy.
Nitajaribu Tai alivyosema.
Wilson.
On Wed, Jul 10, 2013 at 10:54 PM, OpenCastLabs Consulting < dkipkulei@gmail.com> wrote:
Thuo, I deal with these all the time. Send me the file I crack it in a min.
David Kandie OpenCastLabs Consulting www.opencast-labs.com Advanced Excel Training in Eastern Africa & DR Congo
------------------------------------------------------- Budgeting, Forecasting & Financial Modeling Training - www.opencastlabs.com
On Wed, Jul 10, 2013 at 5:12 PM, Thuo Wilson <lixton@gmail.com>wrote:
> Hey, > > Supposing i had an excel document like the attached with a thousands > rows and within a cell are multiple values separated by commas i want to > spread to other rows. How do i do it? > > See example; How do i spread column 3 so that each item/valu appear > on its own row. Mind you i have a thousands row with 3rd column with values > in excess of 100, so manual is attainable but tiresome. > > Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, > 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda > Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, > 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina > Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, > 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, > 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed > 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, > 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 > Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300 > > > _______________________________________________ > skunkworks mailing list > skunkworks@lists.my.co.ke > ------------ > List info, subscribe/unsubscribe > http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks > ------------ > > Skunkworks Rules > http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 > ------------ > Other services @ http://my.co.ke >
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Isaac Kiplagat. KIP®
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Wilson, I know this is late, but if still looking for a solution, I had sometime to kill and I have made you a Macro! You can modify and change the workings. Basic VBA. Sorry no comments! I hate doing that. On Wed, Jul 10, 2013 at 9:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On 14 July 2013 19:26, Philip Musyoki <pmusyoki@gmail.com> wrote:
Wilson,
I know this is late, but if still looking for a solution, I had sometime to kill and I have made you a Macro!
You can modify and change the workings. Basic VBA. Sorry no comments! I hate doing that.
Hehe, I have tried macro way in libre office and no luck! I will check the madirisha office and update. WIlson.
On Wed, Jul 10, 2013 at 9:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
@Thuo, Musyoki's macro is working perfectly on my LibreOffice 4.0 (Mint 15) I even deleted the output worksheet (after which I had to create a new sheet - with same name, otherwise it wont work) and it still works. I am sure, of course, that you have enabled Macros. On Mon, Jul 15, 2013 at 10:43 AM, Thuo Wilson <lixton@gmail.com> wrote:
On 14 July 2013 19:26, Philip Musyoki <pmusyoki@gmail.com> wrote:
Wilson,
I know this is late, but if still looking for a solution, I had sometime to kill and I have made you a Macro!
You can modify and change the workings. Basic VBA. Sorry no comments! I hate doing that.
Hehe,
I have tried macro way in libre office and no luck!
I will check the madirisha office and update.
WIlson.
On Wed, Jul 10, 2013 at 9:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Agreed, the solution by @Philip works perfectly. On Mon, Jul 15, 2013 at 11:13 AM, Bwana Lawi <mail2lawi@gmail.com> wrote:
@Thuo,
Musyoki's macro is working perfectly on my LibreOffice 4.0 (Mint 15)
I even deleted the output worksheet (after which I had to create a new sheet - with same name, otherwise it wont work) and it still works.
I am sure, of course, that you have enabled Macros.
On Mon, Jul 15, 2013 at 10:43 AM, Thuo Wilson <lixton@gmail.com> wrote:
On 14 July 2013 19:26, Philip Musyoki <pmusyoki@gmail.com> wrote:
Wilson,
I know this is late, but if still looking for a solution, I had sometime to kill and I have made you a Macro!
You can modify and change the workings. Basic VBA. Sorry no comments! I hate doing that.
Hehe,
I have tried macro way in libre office and no luck!
I will check the madirisha office and update.
WIlson.
On Wed, Jul 10, 2013 at 9:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Anthony Tai "A good head and a good heart are always a formidable combination."
On 15 July 2013 11:13, Bwana Lawi <mail2lawi@gmail.com> wrote:
@Thuo,
Musyoki's macro is working perfectly on my LibreOffice 4.0 (Mint 15)
I even deleted the output worksheet (after which I had to create a new sheet - with same name, otherwise it wont work) and it still works.
I am sure, of course, that you have enabled Macros.
Thanks, Yah i have enabled Macros :) Mine just says " just done it" but nithing beyond that, Will debug in a while. Thanks alot guys. Looks a good piece of code i must say --- think its a me issue not working... ==== Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1 Option Explicit Function LastRow() On Error GoTo Err With ThisWorkbook.Sheets("Original") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With Exit Function Err: LastRow = 0 Exit Function End Function Sub ProcessRows() On Error GoTo Err Dim RowCounter, CharacterCounter, LastCommaPosition, CurrentRow, ItemRowCounter As Integer Dim RowValue, RowInValue As String If LastRow > 0 Then CurrentRow = 1 For RowCounter = 1 To LastRow ItemRowCounter = 1 RowValue = ThisWorkbook.Sheets("Original").Range("C" & RowCounter).Value RowInValue = RowValue LastCommaPosition = 1 If InStr(1, RowValue, ",") > 1 Then For CharacterCounter = 1 To Len(RowValue) If Mid(RowValue, CharacterCounter, 1) = "," Or CharacterCounter = Len(RowValue) Then RowInValue = Replace(Mid(RowValue, LastCommaPosition, (CharacterCounter - LastCommaPosition) + IIf(CharacterCounter = Len(RowValue), 1, 0)), ",", "") ThisWorkbook.Sheets("Output").Range("A" & CurrentRow).Value = RowCounter ThisWorkbook.Sheets("Output").Range("B" & CurrentRow).Value = ItemRowCounter ThisWorkbook.Sheets("Output").Range("C" & CurrentRow).Value = CurrentRow ThisWorkbook.Sheets("Output").Range("D" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("A" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("E" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("B" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("F" & CurrentRow).Value = RowInValue CurrentRow = CurrentRow + 1 ItemRowCounter = ItemRowCounter + 1 LastCommaPosition = CharacterCounter End If Next CharacterCounter Else ThisWorkbook.Sheets("Output").Range("A" & CurrentRow).Value = RowCounter ThisWorkbook.Sheets("Output").Range("B" & CurrentRow).Value = ItemRowCounter ThisWorkbook.Sheets("Output").Range("C" & CurrentRow).Value = CurrentRow ThisWorkbook.Sheets("Output").Range("D" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("A" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("E" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("B" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("F" & CurrentRow).Value = RowInValue CurrentRow = CurrentRow + 1 ItemRowCounter = ItemRowCounter + 1 ItemRowCounter = ItemRowCounter + 1 End If Next RowCounter End If MsgBox "Just done it!" Err: Exit Sub End Sub ==== Wilson./
On Mon, Jul 15, 2013 at 10:43 AM, Thuo Wilson <lixton@gmail.com> wrote:
On 14 July 2013 19:26, Philip Musyoki <pmusyoki@gmail.com> wrote:
Wilson,
I know this is late, but if still looking for a solution, I had sometime to kill and I have made you a Macro!
You can modify and change the workings. Basic VBA. Sorry no comments! I hate doing that.
Hehe,
I have tried macro way in libre office and no luck!
I will check the madirisha office and update.
WIlson.
On Wed, Jul 10, 2013 at 9:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
@thuo. Check the next worksheet. Your solution is there. Depending on how many times you clicked on the button. On Mon, Jul 15, 2013 at 1:30 PM, Thuo Wilson <lixton@gmail.com> wrote:
On 15 July 2013 11:13, Bwana Lawi <mail2lawi@gmail.com> wrote:
@Thuo,
Musyoki's macro is working perfectly on my LibreOffice 4.0 (Mint 15)
I even deleted the output worksheet (after which I had to create a new sheet - with same name, otherwise it wont work) and it still works.
I am sure, of course, that you have enabled Macros.
Thanks,
Yah i have enabled Macros :)
Mine just says " just done it" but nithing beyond that, Will debug in a while.
Thanks alot guys.
Looks a good piece of code i must say --- think its a me issue not working...
====
Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1 Option Explicit
Function LastRow() On Error GoTo Err
With ThisWorkbook.Sheets("Original") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With
Exit Function
Err: LastRow = 0 Exit Function
End Function
Sub ProcessRows() On Error GoTo Err
Dim RowCounter, CharacterCounter, LastCommaPosition, CurrentRow, ItemRowCounter As Integer Dim RowValue, RowInValue As String
If LastRow > 0 Then CurrentRow = 1
For RowCounter = 1 To LastRow ItemRowCounter = 1 RowValue = ThisWorkbook.Sheets("Original").Range("C" & RowCounter).Value RowInValue = RowValue LastCommaPosition = 1
If InStr(1, RowValue, ",") > 1 Then
For CharacterCounter = 1 To Len(RowValue)
If Mid(RowValue, CharacterCounter, 1) = "," Or CharacterCounter = Len(RowValue) Then RowInValue = Replace(Mid(RowValue, LastCommaPosition, (CharacterCounter - LastCommaPosition) + IIf(CharacterCounter = Len(RowValue), 1, 0)), ",", "") ThisWorkbook.Sheets("Output").Range("A" & CurrentRow).Value = RowCounter ThisWorkbook.Sheets("Output").Range("B" & CurrentRow).Value = ItemRowCounter ThisWorkbook.Sheets("Output").Range("C" & CurrentRow).Value = CurrentRow ThisWorkbook.Sheets("Output").Range("D" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("A" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("E" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("B" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("F" & CurrentRow).Value = RowInValue CurrentRow = CurrentRow + 1 ItemRowCounter = ItemRowCounter + 1 LastCommaPosition = CharacterCounter End If
Next CharacterCounter
Else ThisWorkbook.Sheets("Output").Range("A" & CurrentRow).Value = RowCounter ThisWorkbook.Sheets("Output").Range("B" & CurrentRow).Value = ItemRowCounter ThisWorkbook.Sheets("Output").Range("C" & CurrentRow).Value = CurrentRow ThisWorkbook.Sheets("Output").Range("D" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("A" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("E" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("B" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("F" & CurrentRow).Value = RowInValue CurrentRow = CurrentRow + 1 ItemRowCounter = ItemRowCounter + 1 ItemRowCounter = ItemRowCounter + 1 End If
Next RowCounter End If
MsgBox "Just done it!"
Err: Exit Sub
End Sub
====
Wilson./
On Mon, Jul 15, 2013 at 10:43 AM, Thuo Wilson <lixton@gmail.com> wrote:
On 14 July 2013 19:26, Philip Musyoki <pmusyoki@gmail.com> wrote:
Wilson,
I know this is late, but if still looking for a solution, I had sometime to kill and I have made you a Macro!
You can modify and change the workings. Basic VBA. Sorry no comments! I hate doing that.
Hehe,
I have tried macro way in libre office and no luck!
I will check the madirisha office and update.
WIlson.
On Wed, Jul 10, 2013 at 9:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Anthony Tai "A good head and a good heart are always a formidable combination."
On 15 July 2013 13:36, Anthony Tai <jicholatai@gmail.com> wrote:
@thuo. Check the next worksheet. Your solution is there. Depending on how many times you clicked on the button.
Its not - if you meant the Output sheet.
Havent got time to re-look into this but will and update. If its working on your libre - it will on mine too. WIlson.
On Mon, Jul 15, 2013 at 1:30 PM, Thuo Wilson <lixton@gmail.com> wrote:
On 15 July 2013 11:13, Bwana Lawi <mail2lawi@gmail.com> wrote:
@Thuo,
Musyoki's macro is working perfectly on my LibreOffice 4.0 (Mint 15)
I even deleted the output worksheet (after which I had to create a new sheet - with same name, otherwise it wont work) and it still works.
I am sure, of course, that you have enabled Macros.
Thanks,
Yah i have enabled Macros :)
Mine just says " just done it" but nithing beyond that, Will debug in a while.
Thanks alot guys.
Looks a good piece of code i must say --- think its a me issue not working...
====
Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1 Option Explicit
Function LastRow() On Error GoTo Err
With ThisWorkbook.Sheets("Original") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With
Exit Function
Err: LastRow = 0 Exit Function
End Function
Sub ProcessRows() On Error GoTo Err
Dim RowCounter, CharacterCounter, LastCommaPosition, CurrentRow, ItemRowCounter As Integer Dim RowValue, RowInValue As String
If LastRow > 0 Then CurrentRow = 1
For RowCounter = 1 To LastRow ItemRowCounter = 1 RowValue = ThisWorkbook.Sheets("Original").Range("C" & RowCounter).Value RowInValue = RowValue LastCommaPosition = 1
If InStr(1, RowValue, ",") > 1 Then
For CharacterCounter = 1 To Len(RowValue)
If Mid(RowValue, CharacterCounter, 1) = "," Or CharacterCounter = Len(RowValue) Then RowInValue = Replace(Mid(RowValue, LastCommaPosition, (CharacterCounter - LastCommaPosition) + IIf(CharacterCounter = Len(RowValue), 1, 0)), ",", "") ThisWorkbook.Sheets("Output").Range("A" & CurrentRow).Value = RowCounter ThisWorkbook.Sheets("Output").Range("B" & CurrentRow).Value = ItemRowCounter ThisWorkbook.Sheets("Output").Range("C" & CurrentRow).Value = CurrentRow ThisWorkbook.Sheets("Output").Range("D" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("A" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("E" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("B" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("F" & CurrentRow).Value = RowInValue CurrentRow = CurrentRow + 1 ItemRowCounter = ItemRowCounter + 1 LastCommaPosition = CharacterCounter End If
Next CharacterCounter
Else ThisWorkbook.Sheets("Output").Range("A" & CurrentRow).Value = RowCounter ThisWorkbook.Sheets("Output").Range("B" & CurrentRow).Value = ItemRowCounter ThisWorkbook.Sheets("Output").Range("C" & CurrentRow).Value = CurrentRow ThisWorkbook.Sheets("Output").Range("D" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("A" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("E" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("B" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("F" & CurrentRow).Value = RowInValue CurrentRow = CurrentRow + 1 ItemRowCounter = ItemRowCounter + 1 ItemRowCounter = ItemRowCounter + 1 End If
Next RowCounter End If
MsgBox "Just done it!"
Err: Exit Sub
End Sub
====
Wilson./
On Mon, Jul 15, 2013 at 10:43 AM, Thuo Wilson <lixton@gmail.com> wrote:
On 14 July 2013 19:26, Philip Musyoki <pmusyoki@gmail.com> wrote:
Wilson,
I know this is late, but if still looking for a solution, I had sometime to kill and I have made you a Macro!
You can modify and change the workings. Basic VBA. Sorry no comments! I hate doing that.
Hehe,
I have tried macro way in libre office and no luck!
I will check the madirisha office and update.
WIlson.
On Wed, Jul 10, 2013 at 9:12 PM, Thuo Wilson <lixton@gmail.com> wrote:
Hey,
Supposing i had an excel document like the attached with a thousands rows and within a cell are multiple values separated by commas i want to spread to other rows. How do i do it?
See example; How do i spread column 3 so that each item/valu appear on its own row. Mind you i have a thousands row with 3rd column with values in excess of 100, so manual is attainable but tiresome.
Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Anthony Tai
"A good head and a good heart are always a formidable combination."
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On 15 July 2013 16:06, Thuo Wilson <lixton@gmail.com> wrote:
On 15 July 2013 13:36, Anthony Tai <jicholatai@gmail.com> wrote:
@thuo. Check the next worksheet. Your solution is there. Depending on how many times you clicked on the button.
Its not - if you meant the Output sheet.
Havent got time to re-look into this but will and update. If its working on your libre - it will on mine too.
WIlson.
On Mon, Jul 15, 2013 at 1:30 PM, Thuo Wilson <lixton@gmail.com> wrote:
On 15 July 2013 11:13, Bwana Lawi <mail2lawi@gmail.com> wrote:
@Thuo,
Musyoki's macro is working perfectly on my LibreOffice 4.0 (Mint 15)
I even deleted the output worksheet (after which I had to create a new sheet - with same name, otherwise it wont work) and it still works.
I am sure, of course, that you have enabled Macros.
Thanks,
Yah i have enabled Macros :)
Mine just says " just done it" but nithing beyond that, Will debug in a while.
Thanks alot guys.
Looks a good piece of code i must say --- think its a me issue not working...
====
Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1 Option Explicit
Function LastRow() On Error GoTo Err
With ThisWorkbook.Sheets("Original") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With
Exit Function
Err: LastRow = 0 Exit Function
End Function
Sub ProcessRows() On Error GoTo Err
Dim RowCounter, CharacterCounter, LastCommaPosition, CurrentRow, ItemRowCounter As Integer Dim RowValue, RowInValue As String
If LastRow > 0 Then CurrentRow = 1
For RowCounter = 1 To LastRow ItemRowCounter = 1 RowValue = ThisWorkbook.Sheets("Original").Range("C" & RowCounter).Value RowInValue = RowValue LastCommaPosition = 1
If InStr(1, RowValue, ",") > 1 Then
For CharacterCounter = 1 To Len(RowValue)
If Mid(RowValue, CharacterCounter, 1) = "," Or CharacterCounter = Len(RowValue) Then RowInValue = Replace(Mid(RowValue, LastCommaPosition, (CharacterCounter - LastCommaPosition) + IIf(CharacterCounter = Len(RowValue), 1, 0)), ",", "") ThisWorkbook.Sheets("Output").Range("A" & CurrentRow).Value = RowCounter ThisWorkbook.Sheets("Output").Range("B" & CurrentRow).Value = ItemRowCounter ThisWorkbook.Sheets("Output").Range("C" & CurrentRow).Value = CurrentRow ThisWorkbook.Sheets("Output").Range("D" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("A" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("E" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("B" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("F" & CurrentRow).Value = RowInValue CurrentRow = CurrentRow + 1 ItemRowCounter = ItemRowCounter + 1 LastCommaPosition = CharacterCounter End If
Next CharacterCounter
Else ThisWorkbook.Sheets("Output").Range("A" & CurrentRow).Value = RowCounter ThisWorkbook.Sheets("Output").Range("B" & CurrentRow).Value = ItemRowCounter ThisWorkbook.Sheets("Output").Range("C" & CurrentRow).Value = CurrentRow ThisWorkbook.Sheets("Output").Range("D" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("A" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("E" & CurrentRow).Value = ThisWorkbook.Sheets("Original").Range("B" & RowCounter).Value ThisWorkbook.Sheets("Output").Range("F" & CurrentRow).Value = RowInValue CurrentRow = CurrentRow + 1 ItemRowCounter = ItemRowCounter + 1 ItemRowCounter = ItemRowCounter + 1 End If
Next RowCounter End If
MsgBox "Just done it!"
Err: Exit Sub
End Sub
====
Wilson./
On Mon, Jul 15, 2013 at 10:43 AM, Thuo Wilson <lixton@gmail.com> wrote:
On 14 July 2013 19:26, Philip Musyoki <pmusyoki@gmail.com> wrote:
Wilson,
I know this is late, but if still looking for a solution, I had sometime to kill and I have made you a Macro!
You can modify and change the workings. Basic VBA. Sorry no comments! I hate doing that.
Thanks Guys, Went to the code line by line and noticed my working sheet must be remaned to Original or change the word! Hadnt been keen to notice the "Original" to replace my working sheet! Working! Wilson.
Hehe,
I have tried macro way in libre office and no luck!
I will check the madirisha office and update.
WIlson.
On Wed, Jul 10, 2013 at 9:12 PM, Thuo Wilson <lixton@gmail.com>wrote:
> Hey, > > Supposing i had an excel document like the attached with a thousands > rows and within a cell are multiple values separated by commas i want to > spread to other rows. How do i do it? > > See example; How do i spread column 3 so that each item/valu appear > on its own row. Mind you i have a thousands row with 3rd column with values > in excess of 100, so manual is attainable but tiresome. > > Angola Mobile 2449 Anguilla Fixed 1264 Anguilla Mobile 126453, > 126458, 126472, 126477, 1264235, 1264469, 1264476 Antigua & Barbuda > Fixed 1268 Antigua & Barbuda Mobile 126872, 126873, 126876, > 126877, 126878, 1268464, 1268764 Argentina Fixed 54 Argentina > Mobile 549 Armenia Fixed 374 Armenia Mobile 3744, 3745, 3747, > 3749, 37460 Aruba Fixed 297 Aruba Mobile 2975, 2976, 2979, 29756, > 29758, 29759, 29773, 29774, 29796, 29799... Ascension Island Fixed > 247 Australia Fixed 61, 6128, 6129, 6138, 6139, 6173, 6186, 6187, > 61261, 61262 Australia Mobile 614, 6115, 6116, 6117, 6118, 6119 > Australia Tollfree 611800 Australia Tollfree (Series 611300) 611300 > > > _______________________________________________ > skunkworks mailing list > skunkworks@lists.my.co.ke > ------------ > List info, subscribe/unsubscribe > http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks > ------------ > > Skunkworks Rules > http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 > ------------ > Other services @ http://my.co.ke >
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Anthony Tai
"A good head and a good heart are always a formidable combination."
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
participants (13)
-
Alex Mwega -
Anthony Tai -
Benjamin -
Bwana Lawi -
Isaac Kiplagat -
Kennedy Kairu Kariuki -
Laban Mwangi -
OpenCastLabs Consulting -
Philip Musyoki -
Richard Gathogo -
TheMburu George -
Thuo Wilson -
wa