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

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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),"""")"

'Headers
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.
 

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
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!!!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,102,665
Messages
5,488,177
Members
407,630
Latest member
Mehezabin

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top