View Single Post
 
Old 05-19-2012, 01:25 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote