Generate Report from Data Set based on multiple criteria

[jflaherty]

Hi all,

Long-time lurker; first-time poster. I'm coming up against a bit of a brick wall with my current task and I thought you'd be able to help. My problem is this:

I have a set of data, consisting of a list of names (col A), category A, B or C (col B) and number of days overdue (col C)

I would like to run a report on the next tab which returns the names based on the following arguments:

Category A names:
- return the names that are between 0-30 days overdue in col A
- return the names that are between 30-90 days overdue in col B
- return the names that are over 90 days overdue in col C

and so on for Category B and C names.

Ideally, I'd like the report tab to group all the names at the top of the screen (rather than a load of blank cells which would most likely come from a vlookup/if combination.

I suspect I need a macro for this, which is where my expertise runs out, but any help would be greatly appreciated.

I've uploaded the (anonymised!) file here: https://uploadfiles.io/qctq8

Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub GetOverdue()
   Dim Cary As Variant, Dary As Variant
   Dim i As Long, j As Long, k As Long
   
   Cary = Array("A", "B", "C")
   Dary = Array(0, 30, 31, 90, 91, 1000)
   
   Application.ScreenUpdating = False
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = 0 To UBound(Cary)
         For j = 0 To UBound(Dary) Step 2
            k = k + 1
            .Range("A1:C1").AutoFilter 2, Cary(i)
            .Range("A1:C1").AutoFilter 3, ">=" & Dary(j), xlAnd, "<=" & Dary(j + 1)
            .AutoFilter.Range.Columns(1).Copy Sheets("report").Cells(6, k)
         Next j
         k = k + 1
      Next i
      .AutoFilterMode = False
   End With
         
End Sub

 

[jflaherty]

Hi & welcome to MrExcel.
How about

Sub GetOverdue()
   Dim Cary As Variant, Dary As Variant
   Dim i As Long, j As Long, k As Long
   
   Cary = Array("A", "B", "C")
   Dary = Array(0, 30, 31, 90, 91, 1000)
   
   Application.ScreenUpdating = False
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = 0 To UBound(Cary)
         For j = 0 To UBound(Dary) Step 2
            k = k + 1
            .Range("A1:C1").AutoFilter 2, Cary(i)
            .Range("A1:C1").AutoFilter 3, ">=" & Dary(j), xlAnd, "<=" & Dary(j + 1)
            .AutoFilter.Range.Columns(1).Copy Sheets("report").Cells(6, k)
         Next j
         k = k + 1
      Next i
      .AutoFilterMode = False
   End With
         
End Sub

You, sir, are a gentleman and a scholar. Works perfectly, thank you very much!

 

[Fluff]

Glad to help & thanks for the feedback