Thread: Formula/macro/VBA to match top 5 outstanding amounts to an offset name. Thanks:  1 Post #5293194 (1) Likes:  1 Post #5293194 (1)

1. Re: Formula/macro/VBA to match top 5 outstanding amounts to an offset name.

Show the formula you''re trying.

M.

2. Re: Formula/macro/VBA to match top 5 outstanding amounts to an offset name.

See if this does what you need

Code:
```Sub aTest()
Dim lastRow As Long

'Get the last row with data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Formula in Column V
Range("V4:V" & lastRow).Formula = "=IF(ISNUMBER(B4),B4,"""")"
'Formula in Column W
Range("W4:W" & lastRow).Formula = _
"=IF(ISNUMBER(V4),LOOKUP(2,1/((A\$4:A4<>""INV"")*(A\$4:A4<>""Customer Totals:"")),A\$4:A4),"""")"

Range("X1:AA1") = Array("Exclude", "Large", "Value", "Customer")
'Exclusion list
Range("X2:X5") = Application.Transpose(Array("Gmit", "BG5", "GRM", "Gmit SC"))
'Large
Range("Y2:Y6") = Application.Transpose(Array(1, 2, 3, 4, 5))

'Formulas to get top 5 values
With Range("Z2:Z6")
.Formula = _
"=AGGREGATE(14,6,V\$4:V\$" & lastRow & "/ISNA(MATCH(W\$4:W\$" & lastRow & ",X\$2:X\$5,0)),Y2)"
.NumberFormat = "#,##0.00"
End With
'Formulas to get top 5 customers
Range("AA2:AA6").Formula = "=INDEX(W\$4:W\$" & lastRow & ",MATCH(Z2,V\$4:V\$" & lastRow & ",0))"

End Sub```
M.

3. Re: Formula/macro/VBA to match top 5 outstanding amounts to an offset name.

Awesome!!

How can I change the formula for column V and W so as not to not pick up the last row?

The last row has "Report Totals" with \$ amounts in columns B:F.

The code is picking this up and using it as if it were a customer. It is showing as the number 1 customer.

Other than that this works like a charm!!!

4. Re: Formula/macro/VBA to match top 5 outstanding amounts to an offset name.

Change

'Get the last row with data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
to
'Get the last row with data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row - 1

M.

5. Re: Formula/macro/VBA to match top 5 outstanding amounts to an offset name.

Thank you for sharing your knowledge.

Everything is excellent!

6. Re: Formula/macro/VBA to match top 5 outstanding amounts to an offset name.

You are welcome. Thanks for the feedback.

M.