Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Location
    NE USA
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,269
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

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

    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.

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    NE USA
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    1 Widen Columns a:u of newly exported application report
    2 Enter formula into "H1" =MID(B5,13,10)
    3 Enter formula into "I1" =EOMONTH(B6,-1)
    4 GOTO Special - Last Cell This should be in column "M"
    5 Enter "x"
    6 Copy cell with "x" and paste to columns N:Y in same row M:Y should be x's in same row as last cell
    7 Enter formula into "N5" =if(left($h4,3)="net",mid($h4,5,2)," ")
    8 Copy formula in N5:O5 to the bottom
    9 Enter formula into "O5" =IFERROR(IF(OR(L$1-H8>1000,L$1-H8<0)," ",L$1-H8)," ")
    10 Copy formula in O5:O5 to the bottom
    11 Enter template dys overdue categories Q3:U3 Q3= "1 - 7 days"
    12 R3= "8 - 14 days"
    13 S3= "15 - 30 days"
    14 T3= "31 - 60 days"
    15 U3= "60+ days"
    16 Enter 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,"")
    21 Copy Q5:U5 to the bottom
    22 Enter 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)
    28 Format Q1:U1 Yellow fill & Calibri 12 font & Borders all
    29 Format Z2:Z6 and AB2:AB6 Yellow fill & Calibri 12 font & Borders all
    30 Copy Q1:U1 of current workbook
    31 open 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?
    33 Paste Special in D8 of newly opened workbook Paste Special (value and number format & Transpose) into cell D8 of newly opened workbook
    34 copy ab2:ab6 of "Aging" Paste Special (value and number format) into cell a16 of newly opened workbook
    35 Copy Z2:Z6 of current workbook Paste Special (value and number format) into cell a16 of newly opened workbook
    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

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,269
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

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

    About this kind of formula
    =LOOKUP(2,1/((condition1)*(condition2)),result_vector)

    See the excellent explanation provided by Aladin Akyurek at
    https://www.mrexcel.com/forum/excel-...tml#post492425

    Hope this helps

    M.

  5. #5
    Board Regular
    Join Date
    Apr 2009
    Location
    NE USA
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,269
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

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

    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


    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 by Marcelo Branco; Jul 2nd, 2019 at 12:03 PM.

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    NE USA
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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),"")"


    Not sure what I am doing wrong.

    Thank you!!!!!

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,269
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

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

    You are welcome. Thanks for the feedback.

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

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

    M.
    Last edited by Marcelo Branco; Jul 3rd, 2019 at 01:46 PM.

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    NE USA
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Location
    NE USA
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •