Convert single colum/multiple rows to multiple colums.

Hi,

I have a .dat file when opened with Excel it has only 1 column with 7 rows per
entry. I would like to delete some rows and convert the rest to something
like I have below. Any chance this can be done? It's quite large. 51,793
rows.

TIA

Jeff


Col 1 Col 2 Col 3
User Name Date Artist

users name 06/04/2008 Lenny Kravitz - 2000 - Greatest Hits

users name 02/03/2007 Cold War Kids - 2007 - Robbers & Cowards


-- Table: {1}
{
"music"
"users name",
"06/04/2008",
"Lenny Kravitz - 2000 - Greatest Hits",
},

-- Table: {2}
{
"music",
"users name",
"02/03/2007",
"Cold War Kids - 2007 - Robbers & Cowards",
},

etc
etc

Originally Posted by mudraker

Try this macro It works on the small sample data you supplied

It did not work at first but I think the problem is there are no spaces between entries. It appears when I pasted in my post it created a space.

I tried the macro with a space on line 8 etc and it worked.

-- Table: {2} <----Line 8
-- Table: {3} <----Line 15
-- Table: {4} <----Line 22 etc....


Sorry,


TIA

M

Originally Posted by mudraker

If your data reapeats every 7 rows try changing

That worked thanks. Of course one of the entries half way through the dat file was missing a bracket so it took me and hour to figure that out...

Also, if you don't mind. I'm quite new with this and once saved I can't format the date cells so I can sort them? Year doesn't seem to matter. I replaced the "quotes" with nothing and I'm not sure if that matters.

M



02/03/2007
02/03/2008
02/03/2008
02/03/2008
02/03/2008
02/03/2008
02/03/2008
02/04/2007

Been working slowly on a non-macro solution while others have posted, but I'll offer this anyway.
Regards
Mike

Originally Posted by merlinxl

It did not work at first but I think the problem is there are no spaces between entries. It appears when I pasted in my post it created a space.

I tried the macro with a space on line 8 etc and it worked.

-- Table: {2} <----Line 8
-- Table: {3} <----Line 15
-- Table: {4} <----Line 22 etc....


Sorry,


TIA

M

The macro leaves "quotes" around each entry. Is it possible to remove them?

try

Sub test()
Dim i As Long, n As Long
For i = 8 To Cells(Rows.Count, "a").End(xlUp).Row Step 7
    n = n + 1
    Cells(n, "c").Reszie(,7).Value = _
            Evaluate("transpose(transpose(" & _
            Cells(i + 1,"a").Resize(7).Address & "))")
Next
Range("b1:b" & n).Replace what:=Chr(34), replacement:=""
End Sub

Originally Posted by jindon

try

Sub test()
Dim i As Long, n As Long
For i = 8 To Cells(Rows.Count, "a").End(xlUp).Row Step 7
    n = n + 1
    Cells(n, "c").Reszie(,7).Value = _
            Evaluate("transpose(transpose(" & _
            Cells(i + 1,"a").Resize(7).Address & "))")
Next
Range("b1:b" & n).Replace what:=Chr(34), replacement:=""
End Sub

That didn't work.
Run-time error 438
Object doesn't support this property or method.
Debug
Cells(n, "c").Reszie(, 7).Value = _
Evaluate("transpose(transpose(" & _
Cells(i + 1, "a").Resize(7).Address & "))")

Thanks for your help,

M

Should be

    Cells(n, "c").Reszie(,7).Value = _
            Evaluate("transpose(" & Cells(i + 1,"a").Resize(7).Address & ")")

Originally Posted by mudraker

Try this macro

It works on the small sample data you supplied

If it needs tweeking can you poast a larger sample - preferable a copy of the dat file

Sub For_Thread_646410()
   Dim lTrow As Long
   Dim l4Row As Long
   Dim i4Cnt As Integer
   Dim sTxt As String
   
   For l4Row = 1 To Cells(Rows.Count, "a").End(xlUp).Row Step 8
      lTrow = lTrow + 1
      For i4Cnt = 2 To 4 Step 1
         sTxt = Cells(l4Row + (1 + i4Cnt), "a").Value
         Cells(lTrow, i4Cnt).Value = Left(sTxt, Len(sTxt) - 1)
      Next i4Cnt
   Next l4Row
End Sub

After changing row step to 7, this seems to work the best for me. If there is a way to remove the quotes that remain around the remaining Fields that would be appreciated.

Thanks for all the help,

M