I have a text file with dates arranged as such.

2010-01-02 (i)
2010-05-07 (i)
2010-06-12 (i)
2010-01-02 (i)
2010-05-07 (i)
2010-06-12 (i)
2011-01-05 (i)
2011-05-05 (i)
2011-06-14 (i)

Is there a function or way to arrange the data in a spreadsheet with 2 columns like:

| 2010-01-02 (i)|name1|
| 2010-05-07 (i)|name1|
| 2010-06-12 (i)|name1|  
| 2010-01-02 (i)|name2|
| 2010-05-07 (i)|name2|
| 2010-06-12 (i)|name2|
| 2011-01-05 (i)|name3|
| 2011-05-05 (i)|name3|
| 2011-06-14 (i)|name3|

So the items can be sorted and counted?

I believe I need to write a macro that can loop through column A

  1. !if "()"
  2. cut cell
  3. else paste cell B
  • I've cobbled together something that works in LibreOffice. It's nowhere near elegant, but it does seem to work if one assumes column A in your spreadsheet is your data as above and there are no other contents. Good luck! – geoB Jul 8 '14 at 14:13

Assuming the data is in column A of Sheet1, the following will do the trick:

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Option Explicit
Sub rearrange()
    Dim elements As Integer, rowIndex As Integer, i As Integer
    Dim Name As String
'   LO Basic additions
    Dim Doc As Object
    Dim Sheet As Object
    dim cell as object
    Doc = ThisComponent
    Sheet = Doc.Sheets.getByName("Sheet1")

    Cell = Sheet.getCellRangeByName("D1")

    Cell.formula = "=COUNTA(A1:A104586)"

    elements = cell.value
    rowIndex = 1
    For i = 1 To elements
        If Right(Range("A" & i), 3) <> "(i)" Then
            Name = Range("A" & i)
            Range("B" & rowIndex) = Name
            Range("C" & rowIndex) = Range("A" & i)
            rowIndex = rowIndex + 1
        End If
End Sub
  • I assume this is LO Basic code? When I attached the macro to a button and clicked it, I got this error: Basic Runtime error. Object variable not set. Pointer was on elements line. – rrrfusco Jul 8 '14 at 2:18
  • Sorry - it's VBA, not LO Basic. I hadn't noticed the LO tag until after I built the VBA. Any chance LO can deal with it? If not, I can try to figure out the LO version, but not until later in the week. – geoB Jul 8 '14 at 4:04
  • I read that LO Basic is similar syntax that requires minimal changes. Figured your code was VBA when I saw xlDown. – rrrfusco Jul 8 '14 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.