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:
<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:
<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:
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
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>