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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Threads
1,101,892
Messages
5,483,536
Members
407,397
Latest member
HerbA

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top