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

1. ## 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. ## Re: Additional help needed - VBA coding

https://www.mrexcel.com/forum/excel-...ml#post5303329

M.