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>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Marcelo Branco

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

dstepan

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

dstepan

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

Marcelo Branco

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

dstepan

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

Marcelo Branco

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

dstepan

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

dstepan

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

Watch MrExcel Video

Forum statistics

Threads
1,102,889
Messages
5,489,547
Members
407,698
Latest member
Lotte_4

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