# Additional help needed - VBA coding

#### dstepan

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

https://www.mrexcel.com/forum/excel...ch-top-5-outstanding-amounts-offset-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

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).