Additional help needed - VBA coding
Results 1 to 2 of 2

Thread: Additional help needed - VBA coding
Thanks Thanks: 0 Likes Likes: 0

  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 Additional help needed - VBA coding

    This is in reference to the following thread for the top 5 customers and their respective totals:

    https://www.mrexcel.com/forum/excel-...fset-name.html

    I have tried to make the formula work to eliminate 3 customer names from the top 5 but no success.

    I'm thinking if i can get rid of the totals for the 3 customers in column "V" then the formulas will work, but I can't figure out how to associate the
    amount in Column V to the customer name. (Somehow work backwards using the formula in Column AB??? - maybe???)

    Currently the customer amount (Col V) is on the same line as "Customer Totals" (Col A). The customer name can be 2+ lines above "Customer Totals" depending on the number of "INV" in column "A"

    Below you will find pertinent data.

    Here is the formula in cell V5 where I pulled out the $amount:
    Range("V5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-21]=""Customer Totals:"",RC[-20],0)"

    My ultimate goal is to get this:
    LARGE VALUE ROW CUSTOMER 1000'S
    1 324,711.00 8 ABBY 324.71
    2 291,518.00 25 WOCU 291.52
    3 170,735.55 29 YEVEN 170.74
    4 160,449.25 21 HENRY 160.45
    5 75,489.00 15 COLD 75.49

    These are the formulas to get the above that this community has helped with:
    COL FORMULA
    Z =LARGE(V$4:V$1000,Y10)
    AA =MATCH(Z2,V:V,0)
    AB =LOOKUP(2,1/((A$4:INDEX(A:A,AA10)<>"INV")*(A$4:INDEX(A:A,AA10)<>"Customer Totals:")),A$4:INDEX(A:A,AA10))
    AC BLANK
    AD BLANK
    AE =U15/1000


    Here is a sample data I'm working with. I cannot have the following 3 customers in my Top 5: Gabby, Gabby NC, or Serge:
    A B Cols C:U
    all being utilized
    (just not shown here for brevity) Â….
    V
    1
    2
    3
    4 ABBY ABBY
    5 INV L10466 -
    6 INV L10734 -
    7 INV L10796 -
    8 Customer Totals: $324,711.00 324,711.00
    9 BANAN BANAN -
    10 INV L10792 -
    11 Customer Totals: $56,002.50 56,002.50
    12 COLD COLD -
    13 INV L10713 -
    14 INV L10714 -
    15 Customer Totals: $75,489.00 75,489.00
    16 GABBY GABBY -
    17 INV L10715 -
    18 INV L10756 -
    19 INV L10758 -
    20 Customer Totals: $290,946.70 290,946.70
    21 GABBY NC GABBY NC -
    22 INV L10763 -
    23 Customer Totals: $114,986.50 114,986.50
    24 HALEY HALEY -
    25 INV L10716 -
    26 Customer Totals: $5,446.23 5,446.23
    27 HENRY HENRY -
    28 INV L10649 -
    29 Customer Totals: 160449.25 160,449.25
    30 SERGE SERGE -
    31 INV L10788 -
    32 Customer Totals: $26,250.00 26,250.00
    33 WOCU WOCU -
    34 INV L10797 -
    35 INV L10788 -
    36 Customer Totals: $291,518.00 291,518.00
    37 YEVEN YEVEN -
    38 INV L10686 -
    39 INV L10789 -
    40 Customer Totals: $170,735.55 170,735.55

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

    Default Re: Additional help needed - VBA coding

    See my reply on the other thread
    https://www.mrexcel.com/forum/excel-...ml#post5303329

    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
  •