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

[dstepan]

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

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

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