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

[dstepan]

[TABLE="width: 578"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 3"]Hello all. I have a head banger issue. Any and all help is a greatly appreciated.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]This data is exported from an application.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]I am working with Excel 2016.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]My attachment shows the data I am working with. Obviously there are more columns (Col’s C:U) and many more rows.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]I am trying to complete a 3 day project of extracting specific information from an aging report with macros..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]The final and most difficult step is to find the top 5 most “Outstanding amounts” (Col B or Col V) with the associated “Customer Name” (Col A)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]The first 2 columns show the actual exported data. I created column V to extract the $ amount. Formula: =if(a5=”Customer Totals:”,a5,0).[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]I don’t care if the information is highlighted or put in a table in another part of the spreadsheet (ex: Col’s Y and Z)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD] V [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GNP[/TD]
[TD]GNP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]INV[/TD]
[TD]L10466[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]INV[/TD]
[TD]L10585[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]INV[/TD]
[TD]L10698[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]INV[/TD]
[TD]L10726[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]INV[/TD]
[TD]L10734[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]INV[/TD]
[TD]L10796[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Customer Totals:[/TD]
[TD]$324,711.00[/TD]
[TD] 324,711.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]AMERICA[/TD]
[TD]AMERICA[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]INV[/TD]
[TD]L10792[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Customer Totals:[/TD]
[TD]$56,002.50[/TD]
[TD] 56,002.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]TONAN[/TD]
[TD]TONAN[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]INV[/TD]
[TD]L10713[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]INV[/TD]
[TD]L10714[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Customer Totals:[/TD]
[TD]$75,489.00[/TD]
[TD] 75,489.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]FLYBY[/TD]
[TD]FLYBY[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]INV[/TD]
[TD]L10715[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]INV[/TD]
[TD]L10720[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]INV[/TD]
[TD]L10756[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]INV[/TD]
[TD]L10758[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Customer Totals:[/TD]
[TD]$290,946.70[/TD]
[TD] 290,946.70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]BASIC[/TD]
[TD]BASIC[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]INV[/TD]
[TD]L10763[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Customer Totals:[/TD]
[TD]$114,986.50[/TD]
[TD] 114,986.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]BAY[/TD]
[TD]BAY[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]INV[/TD]
[TD]L10716[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Customer Totals:[/TD]
[TD]$5,446.23[/TD]
[TD] 5,446.23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]BOW[/TD]
[TD]BOW[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]INV[/TD]
[TD]L10649[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Customer Totals:[/TD]
[TD]160449.25[/TD]
[TD] 160,449.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]SOL[/TD]
[TD]SOL[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]INV[/TD]
[TD]L10746[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Customer Totals:[/TD]
[TD]$26,250.00[/TD]
[TD] 26,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]CONSU[/TD]
[TD]CONSU[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]INV[/TD]
[TD]L10797[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]INV[/TD]
[TD]L10788[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]Customer Totals:[/TD]
[TD]$291,518.00[/TD]
[TD] 291,518.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]COVAN[/TD]
[TD]COVAN[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]INV[/TD]
[TD]L10686[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]INV[/TD]
[TD]L10789[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]Customer Totals:[/TD]
[TD]$170,735.55[/TD]
[TD] 170,735.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]DURG[/TD]
[TD]DURG[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]INV[/TD]
[TD]L10793[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]INV[/TD]
[TD]L10800[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]Customer Totals:[/TD]
[TD]$388,000.00[/TD]
[TD] 388,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]ELCH[/TD]
[TD]ELCH[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]INV[/TD]
[TD]L10769[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]Customer Totals:[/TD]
[TD]$3,900.00[/TD]
[TD] 3,900.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]EMERAL[/TD]
[TD]EMERAL[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]INV[/TD]
[TD]L10700[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]Customer Totals:[/TD]
[TD]$2,625.00[/TD]
[TD] 2,625.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]EXEN[/TD]
[TD]EXEN[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]INV[/TD]
[TD]L10778[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]Customer Totals:[/TD]
[TD]$117,000.00[/TD]
[TD] 117,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Show the formula you''re trying.

M.

 

[Marcelo Branco]

See if this does what you need

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]

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]

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]

Thank you for sharing your knowledge.

Everything is excellent!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.