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