Can't get formula to recognize correct data

macropod · #1 05-19-2012, 01:25 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sum Formula in the range with Numeric and NonNumeric data cells	Spanec	Excel	2	01-12-2012 09:15 AM
Changed data base name. Outlook doesn't recognize.	Skip	Outlook	1	10-20-2011 10:55 AM
PowerPoint won't recognize embed codes from YouTube or Vimeo	JulianC	PowerPoint	1	10-04-2011 03:03 PM
Word crashed and now doesn't recognize file as a Word doc?	sara	Word	0	09-15-2010 02:12 PM
Cross-reference together with Mathtype formula is not working correct	Santa_Clause	Word	2	02-02-2010 04:37 AM