I have a spreadsheet that i exported from an old database program, in CSV
format. I have it saved as a xls now, and my data is laid out like:

lastname firstname dob date01 date 02 date 03 ticket01 ticket02
ticket03 (etc)
lastname firstname dob date01 date 02 date 03 ticket01 ticket02
ticket03

I NEED (for importing into an access database)

lastname firstname dob date01 ticket01
lastname firstname dob date02 ticket02
lastname firstname dob date03 ticket03

I have 2365 individual names... some with one date/ticket entry, some with
10....

HELP!?

And all the dates come before all the tickets?

Then this seemed to work ok:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim LastCol As Long
Dim FirstCol As Long
Dim iCol As Long
Dim QtyOfTickets As Double

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4 'ignore the "headers"

oRow = 1
For iRow = FirstRow To LastRow
LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column
QtyOfTickets = (LastCol - FirstCol + 1) / 2
If Int(QtyOfTickets) < QtyOfTickets Then
MsgBox "Something bad happened on row #: " & iRow & vbLf _
& "Not completed!"
Exit Sub
End If
newWks.Cells(oRow, "A").Resize(QtyOfTickets, 3).Value _
= .Cells(iRow, "A").Resize(1, 3).Value
For iCol = 1 To QtyOfTickets
newWks.Cells(oRow, "D").Value _
= .Cells(iRow, FirstCol + iCol - 1).Value
newWks.Cells(oRow, "E").Value _
= .Cells(iRow, FirstCol + iCol - 1).Value
oRow = oRow + 1
Next iCol
Next iRow
End With
End Sub



Fatboymedic wrote:

I have a spreadsheet that i exported from an old database program, in CSV
format. I have it saved as a xls now, and my data is laid out like:

lastname firstname dob date01 date 02 date 03 ticket01 ticket02
ticket03 (etc)
lastname firstname dob date01 date 02 date 03 ticket01 ticket02
ticket03

I NEED (for importing into an access database)

lastname firstname dob date01 ticket01
lastname firstname dob date02 ticket02
lastname firstname dob date03 ticket03

I have 2365 individual names... some with one date/ticket entry, some with
10....

HELP!?



--

Dave Peterson