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

#### Marcelo Branco

##### MrExcel MVP
Show the formula you''re trying.

M.

### 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
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.

#### dstepan

##### Board Regular
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
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.

#### dstepan

##### Board Regular
Thank you for sharing your knowledge.

Everything is excellent!

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Thanks for the feedback.

M.

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