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