Formula/macro/VBA to match top 5 outstanding amounts to an offset name.
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

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

  1. #11
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,223
    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.

    Show the formula you''re trying.

    M.

  2. #12
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,223
    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.

    See if this does what you need

    Code:
    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.

  3. #13
    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.

    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!!!

  4. #14
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,223
    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.

    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.


  5. #15
    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.

    Thank you for sharing your knowledge.

    Everything is excellent!

  6. #16
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,223
    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.

    M.

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
  •