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

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
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)
AB V
1
2
3
4GNPGNP
5INVL10466 -
6INVL10585 -
7INVL10698 -
8INVL10726 -
9INVL10734 -
10INVL10796 -
11Customer Totals:$324,711.00 324,711.00
12AMERICAAMERICA -
13INVL10792 -
14Customer Totals:$56,002.50 56,002.50
15TONANTONAN -
16INVL10713 -
17INVL10714 -
18Customer Totals:$75,489.00 75,489.00
19FLYBYFLYBY -
20INVL10715 -
21INVL10720 -
22INVL10756 -
23INVL10758 -
24Customer Totals:$290,946.70 290,946.70
25BASICBASIC -
26INVL10763 -
27Customer Totals:$114,986.50 114,986.50
28BAYBAY -
29INVL10716 -
30Customer Totals:$5,446.23 5,446.23
31BOWBOW -
32INVL10649 -
33Customer Totals:160449.25 160,449.25
34SOLSOL -
35INVL10746 -
36Customer Totals:$26,250.00 26,250.00
37CONSUCONSU -
38INVL10797 -
39INVL10788 -
40Customer Totals:$291,518.00 291,518.00
41COVANCOVAN -
42INVL10686 -
43INVL10789 -
44Customer Totals:$170,735.55 170,735.55
45DURGDURG -
46INVL10793 -
47INVL10800 -
48Customer Totals:$388,000.00 388,000.00
49ELCHELCH -
50INVL10769 -
51Customer Totals:$3,900.00 3,900.00
52EMERALEMERAL -
53INVL10700 -
54Customer Totals:$2,625.00 2,625.00
55EXENEXEN -
56INVL10778 -
57Customer Totals:$117,000.00 117,000.00

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

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Maybe something like this


Y
Z
AA
AB
1
Large​
Value​
Row​
Customer​
2
1​
388000,00​
48​
DURG​
3
2​
324711,00​
11​
GNP​
4
3​
291518,00​
40​
CONSU​
5
4​
290946,70​
24​
FLYBY​
6
5​
170735,55​
44​
COVAN​

Formula in Z2 copied down
=LARGE(V$4:V$100,Y2)

Formula in AA2 copied down
=MATCH(Z2,V:V,0)

Formula in AB2 copied down
=LOOKUP(2,1/((A$4:INDEX(A:A,AA2)<>"INV")*(A$4:INDEX(A:A,AA2)<>"Customer Totals:")),A$4:INDEX(A:A,AA2))

Hope this helps

M.
 
Upvote 0
Omigosh. Thank you! It works perferctly.

Is this a stupid question? Can you explain the formula entered into AB2? I am always trying to learn new formulas/functions/macros/VBA in excel, but I'm not sure what the formula is saying.

Also, can this table be coded? And if so, what is the code to highlight Z2:Z6 and AB2:AB6 with the yellow highlight

I have alot going on with this spreadsheet (see coding I need to do).

These are the steps to create the information for Month End
1Widen Columns a:uof newly exported application report
2Enter formula into "H1"=MID(B5,13,10)
3Enter formula into "I1"=EOMONTH(B6,-1)
4GOTO Special - Last CellThis should be in column "M"
5Enter "x"
6Copy cell with "x" and paste to columns N:Y in same rowM:Y should be x's in same row as last cell
7Enter formula into "N5"=if(left($h4,3)="net",mid($h4,5,2)," ")
8Copy formula in N5:O5 to the bottom
9Enter formula into "O5"=IFERROR(IF(OR(L$1-H8>1000,L$1-H8<0)," ",L$1-H8)," ")
10Copy formula in O5:O5 to the bottom
11Enter template dys overdue categories Q3:U3Q3= "1 - 7 days"
12R3= "8 - 14 days"
13S3= "15 - 30 days"
14T3= "31 - 60 days"
15U3= "60+ days"
16Enter formulas in Q5:U5=IF($O12<8,$K12,"")
17=IF(AND($O12>0,$O12<15),$K12,"")
18=IF(AND($O12>14,$O12<31),$K12,"")
19=IF(AND($O12>30,$O12<60),$K12,"")
20=IF($O12>59,$K12,"")
21Copy Q5:U5 to the bottom
22Enter formula in Q1:V1=SUM(Q5:BOTTOM)/1000
23=SUM(R5:BOTTOM)/1000
24=SUM(S5:BOTTOM)/1000
25=SUM(T5:BOTTOM)/1000
26=SUM(U5:BOTTOM)/1000
27 =SUM(Q1:U1)
28Format Q1:U1 Yellow fill & Calibri 12 font & Borders all
29Format Z2:Z6 and AB2:AB6 Yellow fill & Calibri 12 font & Borders all
30Copy Q1:U1 of current workbook
31open H:\Accounting\Over\Accounting\Month End Financial Close\2019 Month End Close\May\Overdue reporting template
32*************************How to name a file when folder name changes for each month?
33Paste Special in D8 of newly opened workbookPaste Special (value and number format & Transpose) into cell D8 of newly opened workbook
34copy ab2:ab6 of "Aging"Paste Special (value and number format) into cell a16 of newly opened workbook
35Copy Z2:Z6 of current workbookPaste Special (value and number format) into cell a16 of newly opened workbook

<colgroup><col><col><col></colgroup><tbody>
</tbody>
36. Save workbook as H:\Accounting\Over\Accounting\Month End Financial Close\2019 Month End Close"AR Aging-AR Template.xlsx"
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
H:\Accounting\Over\Accounting\Month End Financial Close\2019 Month End Close\May\Overdue reporting template

<tbody>
</tbody>
</body>
 
Upvote 0
I have a follow up question to Marcelo's response on 6/12/19 11:56 am.

The formulas and setup are working great. However, there are 3 customers that should be overlooked when trying to find the "Top 5 Customers".

These customers are Gmit, BGS GRM, and Gmit SC.

I'm not sure how to incorporate them into the formulas.

I thank you for your help.
 
Upvote 0
Sorry for the late reply - i hadn't seen your new question

Maybe something like this

V
W
X
Y
Z
AA
1
Exclude​
Large​
Value​
Customer​
2
Gmit​
1​
388000,00​
DURG​
3
BG5​
2​
324711,00​
GNP​
4
GRM​
3​
291518,00​
CONSU​
5
Gmit SC​
4​
290946,70​
FLYBY​
6
5​
170735,55​
COVAN​
7
8
9
10
11
324711​
GNP​
12
13
14
56002,5​
AMERICA​
15
16
17
18
75489​
TONAN​
19
20
21
22
23
24
290946,7​
FLYBY​
25
26
27
114986,5​
BASIC​
28
29
30
5446,23​
BAY​
31
32
33
160449,3​
BOW​
34
35
36
26250​
SOL​
37
38
39
40
291518​
CONSU​
41
42
43
44
170735,6​
COVAN​
45
46
47
48
388000​
DURG​
49

<tbody>
</tbody>


Formula in V4 copied down
=IF(ISNUMBER(C4),C4,"")

Formula in W4 copied down
=IF(ISNUMBER(V4),LOOKUP(2,1/((A$4:A4<>"INV")*(A$4:A4<>"Customer Totals:")),A$4:A4),"")

Exclusion list in X2:X5

Formula in Z2 copied down
=AGGREGATE(14,6,V$4:V$1000/ISNA(MATCH(W$4:W$1000,X$2:X$5,0)),Y2)

Formula in AA2 copied down
=INDEX(W$4:W$1000,MATCH(Z2,V$4:V$1000,0))

Hope this helps

M.
 
Last edited:
Upvote 0
I appreciate the help as this has been a challenge and definitely a learning experience for me.

I could use some help in converting the following formulas as I am having issues with reference style R1C1

Formula in W4 copied down
=IF(ISNUMBER(V4),LOOKUP(2,1/((A$4:A4<>"INV")*(A$4:A4<>"Customer Totals:")),A$4:A4),"")

Formula in Z2 copied down
=AGGREGATE(14,6,V$4:V$1000/ISNA(MATCH(W$4:W$1000,X$2:X$5,0)),Y2)

Formula in AA2 copied down
=INDEX(W$4:W$1000,MATCH(Z2,V$4:V$1000,0))



When I first entered the revised formula for V4 in the code it came back with an error.
I revised the formula so it was in R1C1 reference style (the rest of the code uses R1C1 vs A1).

Range("V4").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[-20]),RC[-20],0)"
It worked.

Therefore, I figured I would have to convert the rest of your formulas in the same format.
I tried to do so with the formula in Col W and got this error.

Compile error: Expected:end of statement

Here is what I tried:
Range("W4").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC-1),LOOKUP(2,1/((R$C-22:RC-22<>"INV")*(R$C-22:RC-22<>"Customer Totals:")),R$C-22:RC-22),"")"

<colgroup><col></colgroup><tbody>
</tbody>


Not sure what I am doing wrong.

Thank you!!!!!
 
Upvote 0
You are welcome. Thanks for the feedback.

Why are you using R1C1-style? I think it's complicated and not intuitive:confused:

Why not?
Range("W4').Formula= "=......"

M.
 
Last edited:
Upvote 0
I am working at a temporary office and my computer was originally setup that way. It is a pretty long code. I finally received approval to change my options and so I changed it to A1 reference style. I tried adding the code the way you have it written but it didn't work out.
I think I will have to go back in and change my options back to the R1C1 reference style so your code will be translated.

I'm going to give it a shot now.
 
Upvote 0
Well I tried changing the reference style to R1C! and I am getting the following error:

Compile error: Syntax error

If I change the reference style back to A1 I get this error:

Compile error:
Expected:end of statement (it also highlights the INV in the formula)

Not sure what to do next.

Any ideas?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top