Hi cklassen,
Your data are not exactly structured for ease of processing. It looks rather like it's been downloaded from some other system without any effort to organise it into a useful layout. Your Info sheet, in particular would benefit from being cleaned up via a macro before you attempt any further processing. For example:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim LastRow As Long, i As Long, j As Long
With ThisWorkbook
With .Worksheets("Info")
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To LastRow
If .Cells(i, 28).Value <> "" Then
If .Cells(i, 28).Value = "JobCat" Then
.Cells(i, 29).Value = .Cells(i, 27).Value
.Cells(i, 27).Value = "Tax"
Else
.Cells(i - 1, 28).Value = .Cells(i, 28).Value
.Cells(i, 28).Value = ""
.Cells(i - 1, 29).Value = .Cells(i, 27).Value
.Cells(i, 27).Value = ""
End If
End If
Next
.Range("S1:AD1").Rows.Delete Shift:=xlUp
.Range("S2:S" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
LastRow = .Range("S" & .Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
.Range("L1:R1").EntireColumn.Delete Shift:=xlLeft
j = 2
For i = 3 To LastRow
If .Cells(i, 1).Value <> "" Then
j = i
Else
.Cells(i, 4).Value = .Cells(j, 4).Value
.Cells(i, 5).Value = .Cells(j, 5).Value
.Cells(i, 6).Value = .Cells(j, 6).Value
.Cells(i, 7).Value = .Cells(j, 7).Value
.Cells(i, 8).Value = .Cells(j, 8).Value
.Cells(i, 10).Value = .Cells(j, 10).Value
End If
Next
End With
End With
Application.ScreenUpdating = True
End Sub
Indeed, you could probably achieve the whole of the desired outcome very quickly with a macro and no formulae, but you should still find it much easier to process the data with formulae after the cleanup done by the above macro.