Additional help needed - VBA coding

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
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:
LARGEVALUEROWCUSTOMER1000'S
1 324,711.008ABBY 324.71
2 291,518.0025WOCU 291.52
3 170,735.5529YEVEN 170.74
4 160,449.2521HENRY 160.45
5 75,489.0015COLD 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))

ACBLANK
ADBLANK
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:
ABCols C:U
all being utilized
(just not shown here for brevity) ….
V
1
2
3
4ABBYABBY
5INVL10466 -
6INVL10734 -
7INVL10796 -
8Customer Totals:$324,711.00 324,711.00
9BANANBANAN -
10INVL10792 -
11Customer Totals:$56,002.50 56,002.50
12COLDCOLD -
13INVL10713 -
14INVL10714 -
15Customer Totals:$75,489.00 75,489.00
16GABBYGABBY -
17INVL10715 -
18INVL10756 -
19INVL10758 -
20Customer Totals:$290,946.70 290,946.70
21GABBY NCGABBY NC -
22INVL10763 -
23Customer Totals:$114,986.50 114,986.50
24HALEYHALEY -
25INVL10716 -
26Customer Totals:$5,446.23 5,446.23
27HENRYHENRY -
28INVL10649 -
29Customer Totals:160449.25 160,449.25
30SERGESERGE -
31INVL10788 -
32Customer Totals:$26,250.00 26,250.00
33WOCUWOCU -
34INVL10797 -
35INVL10788 -
36Customer Totals:$291,518.00 291,518.00
37YEVENYEVEN -
38INVL10686 -
39INVL10789 -
40Customer Totals:$170,735.55 170,735.55

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

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,048
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top