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>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top