Converting columns of non-standard formatted data into a table (rows)

[Peter33]

I have data in two columns that I would like to convert into a table. The challenge is that the data fields of my current columns are not consistent and so I do not think I can use the OFFSET function - at least I don't know how.

I've tried so many things only to realize somewhere along the way that my idea won't work. Ugh...it's getting personal: me against the Excel spreadsheet at this point

The attached file has an explanation of what I am trying to to (it is often easier to show instead of try to explain). Any help anyone could provide would be tremendously welcome.

Thanks,
Peter from Seattle

[shg]

Try this:

Sub x()
    Dim cell As Range
    Dim rOut As Range
    Dim sJob As String
    
    Set rOut = Range("D1:F1")
    rOut.Value = Array("Job", "Job Number", "Cost")
    
    For Each cell In Intersect(Columns(1), ActiveSheet.UsedRange)
        Select Case VarType(cell.Value)
            Case vbString
                If cell.Value = "Job" Then sJob = cell.Offset(, 1).Text
            Case vbDouble
                Set rOut = rOut.Offset(1)
                rOut.Value = Array(sJob, cell.Value, cell.Offset(, 1).Value)
        End Select
    Next cell
End Sub

[Mike_S]

Hi, I have modified your spreadsheet to do what you want, it requires two helper columns and a filter. Once you have filtered out the rows that you don't want you can cut and paste the results into a new spreadsheet. Hope that is what you require. Regards, Mike