Additional help needed - VBA coding

D

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:
[TABLE="width: 330"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]LARGE[/TD]
[TD]VALUE[/TD]
[TD]ROW[/TD]
[TD]CUSTOMER[/TD]
[TD][/TD]
[TD][/TD]
[TD]1000'S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] 324,711.00[/TD]
[TD]8[/TD]
[TD]ABBY[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]324.71[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] 291,518.00[/TD]
[TD]25[/TD]
[TD]WOCU[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]291.52[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] 170,735.55[/TD]
[TD]29[/TD]
[TD]YEVEN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]170.74[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] 160,449.25[/TD]
[TD]21[/TD]
[TD]HENRY[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]160.45[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] 75,489.00[/TD]
[TD]15[/TD]
[TD]COLD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]75.49[/TD]
[/TR]
</tbody>[/TABLE]

These are the formulas to get the above that this community has helped with:
[TABLE="width: 330"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]COL [/TD]
[TD]FORMULA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="colspan: 3"] =LARGE(V$4:V$1000,Y10)

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="colspan: 2"]=MATCH(Z2,V:V,0)[/TD]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD="colspan: 6"]=LOOKUP(2,1/((A$4:INDEX(A:A,AA10)<>"INV")*(A$4:INDEX(A:A,AA10)<>"Customer Totals:")),A$4:INDEX(A:A,AA10))

[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]BLANK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]BLANK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]=U15/1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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:
[TABLE="width: 488"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]Cols C:U
all being utilized
(just not shown here for brevity) ….[/TD]
[TD] V[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABBY[/TD]
[TD]ABBY[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]INV[/TD]
[TD]L10466[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]INV[/TD]
[TD]L10734[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]INV[/TD]
[TD]L10796[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$324,711.00[/TD]
[TD] [/TD]
[TD="align: right"]324,711.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]BANAN[/TD]
[TD]BANAN[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]INV[/TD]
[TD]L10792[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$56,002.50[/TD]
[TD] [/TD]
[TD="align: right"]56,002.50[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]COLD[/TD]
[TD]COLD[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]INV[/TD]
[TD]L10713[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]INV[/TD]
[TD]L10714[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$75,489.00[/TD]
[TD] [/TD]
[TD="align: right"]75,489.00[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]GABBY[/TD]
[TD]GABBY[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]INV[/TD]
[TD]L10715[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]INV[/TD]
[TD]L10756[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]INV[/TD]
[TD]L10758[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$290,946.70[/TD]
[TD] [/TD]
[TD="align: right"]290,946.70[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]GABBY NC[/TD]
[TD]GABBY NC[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]INV[/TD]
[TD]L10763[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$114,986.50[/TD]
[TD] [/TD]
[TD="align: right"]114,986.50[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]HALEY[/TD]
[TD]HALEY[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]INV[/TD]
[TD]L10716[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$5,446.23[/TD]
[TD] [/TD]
[TD="align: right"]5,446.23[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]HENRY[/TD]
[TD]HENRY[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]INV[/TD]
[TD]L10649[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]160449.25[/TD]
[TD] [/TD]
[TD="align: right"]160,449.25[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]SERGE[/TD]
[TD]SERGE[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]INV[/TD]
[TD]L10788[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$26,250.00[/TD]
[TD] [/TD]
[TD="align: right"]26,250.00[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]WOCU[/TD]
[TD]WOCU[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]INV[/TD]
[TD]L10797[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]INV[/TD]
[TD]L10788[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$291,518.00[/TD]
[TD] [/TD]
[TD="align: right"]291,518.00[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]YEVEN[/TD]
[TD]YEVEN[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]INV[/TD]
[TD]L10686[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]INV[/TD]
[TD]L10789[/TD]
[TD] [/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]Customer Totals:[/TD]
[TD="align: right"]$170,735.55[/TD]
[TD] [/TD]
[TD="align: right"]170,735.55[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula/macro/VBA to match top 5 outstanding amounts to an offset name.
2
Replies
15
Views
2K
Marcelo Branco
M

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

Share this page

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