Formula/macro/VBA to match top 5 outstanding amounts to an offset name.

[dstepan]

[TABLE="width: 578"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 3"]Hello all. I have a head banger issue. Any and all help is a greatly appreciated.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]This data is exported from an application.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]I am working with Excel 2016.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]My attachment shows the data I am working with. Obviously there are more columns (Col’s C:U) and many more rows.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]I am trying to complete a 3 day project of extracting specific information from an aging report with macros..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]The final and most difficult step is to find the top 5 most “Outstanding amounts” (Col B or Col V) with the associated “Customer Name” (Col A)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]The first 2 columns show the actual exported data. I created column V to extract the $ amount. Formula: =if(a5=”Customer Totals:”,a5,0).[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]I don’t care if the information is highlighted or put in a table in another part of the spreadsheet (ex: Col’s Y and Z)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD] V [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GNP[/TD]
[TD]GNP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]INV[/TD]
[TD]L10466[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]INV[/TD]
[TD]L10585[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]INV[/TD]
[TD]L10698[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]INV[/TD]
[TD]L10726[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]INV[/TD]
[TD]L10734[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]INV[/TD]
[TD]L10796[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Customer Totals:[/TD]
[TD]$324,711.00[/TD]
[TD] 324,711.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]AMERICA[/TD]
[TD]AMERICA[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]INV[/TD]
[TD]L10792[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Customer Totals:[/TD]
[TD]$56,002.50[/TD]
[TD] 56,002.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]TONAN[/TD]
[TD]TONAN[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]INV[/TD]
[TD]L10713[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]INV[/TD]
[TD]L10714[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Customer Totals:[/TD]
[TD]$75,489.00[/TD]
[TD] 75,489.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]FLYBY[/TD]
[TD]FLYBY[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]INV[/TD]
[TD]L10715[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]INV[/TD]
[TD]L10720[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]INV[/TD]
[TD]L10756[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]INV[/TD]
[TD]L10758[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Customer Totals:[/TD]
[TD]$290,946.70[/TD]
[TD] 290,946.70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]BASIC[/TD]
[TD]BASIC[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]INV[/TD]
[TD]L10763[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Customer Totals:[/TD]
[TD]$114,986.50[/TD]
[TD] 114,986.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]BAY[/TD]
[TD]BAY[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]INV[/TD]
[TD]L10716[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Customer Totals:[/TD]
[TD]$5,446.23[/TD]
[TD] 5,446.23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]BOW[/TD]
[TD]BOW[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]INV[/TD]
[TD]L10649[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Customer Totals:[/TD]
[TD]160449.25[/TD]
[TD] 160,449.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]SOL[/TD]
[TD]SOL[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]INV[/TD]
[TD]L10746[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Customer Totals:[/TD]
[TD]$26,250.00[/TD]
[TD] 26,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]CONSU[/TD]
[TD]CONSU[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]INV[/TD]
[TD]L10797[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]INV[/TD]
[TD]L10788[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]Customer Totals:[/TD]
[TD]$291,518.00[/TD]
[TD] 291,518.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]COVAN[/TD]
[TD]COVAN[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]INV[/TD]
[TD]L10686[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]INV[/TD]
[TD]L10789[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]Customer Totals:[/TD]
[TD]$170,735.55[/TD]
[TD] 170,735.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]DURG[/TD]
[TD]DURG[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]INV[/TD]
[TD]L10793[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]INV[/TD]
[TD]L10800[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]Customer Totals:[/TD]
[TD]$388,000.00[/TD]
[TD] 388,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]ELCH[/TD]
[TD]ELCH[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]INV[/TD]
[TD]L10769[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]Customer Totals:[/TD]
[TD]$3,900.00[/TD]
[TD] 3,900.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]EMERAL[/TD]
[TD]EMERAL[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]INV[/TD]
[TD]L10700[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]Customer Totals:[/TD]
[TD]$2,625.00[/TD]
[TD] 2,625.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]EXEN[/TD]
[TD]EXEN[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]INV[/TD]
[TD]L10778[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]Customer Totals:[/TD]
[TD]$117,000.00[/TD]
[TD] 117,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

Y​

[/td][td="bgcolor: #DCE6F1"]

Z​

[/td][td="bgcolor: #DCE6F1"]

AA​

[/td][td="bgcolor: #DCE6F1"]

AB​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Large​

[/td][td]

Value​

[/td][td]

Row​

[/td][td]

Customer​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

388000,00​

[/td][td]

48​

[/td][td]

DURG​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td]

324711,00​

[/td][td]

11​

[/td][td]

GNP​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td]

291518,00​

[/td][td]

40​

[/td][td]

CONSU​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

4​

[/td][td]

290946,70​

[/td][td]

24​

[/td][td]

FLYBY​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

5​

[/td][td]

170735,55​

[/td][td]

44​

[/td][td]

COVAN​

[/td][/tr]
[/table]

Formula in Z2 copied down
=LARGE(V$4:V$100,Y2)

Formula in AA2 copied down
=MATCH(Z2,V:V,0)

Formula in AB2 copied down
=LOOKUP(2,1/((A$4:INDEX(A:A,AA2)<>"INV")*(A$4:INDEX(A:A,AA2)<>"Customer Totals:")),A$4:INDEX(A:A,AA2))

Hope this helps

M.

 

[dstepan]

Omigosh. Thank you! It works perferctly.

Is this a stupid question? Can you explain the formula entered into AB2? I am always trying to learn new formulas/functions/macros/VBA in excel, but I'm not sure what the formula is saying.

Also, can this table be coded? And if so, what is the code to highlight Z2:Z6 and AB2:AB6 with the yellow highlight

I have alot going on with this spreadsheet (see coding I need to do).

[TABLE="width: 825"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]These are the steps to create the information for Month End[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Widen Columns a:u[/TD]
[TD]of newly exported application report[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Enter formula into "H1"[/TD]
[TD]=MID(B5,13,10)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Enter formula into "I1"[/TD]
[TD]=EOMONTH(B6,-1)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GOTO Special - Last Cell[/TD]
[TD]This should be in column "M"[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Enter "x"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Copy cell with "x" and paste to columns N:Y in same row[/TD]
[TD]M:Y should be x's in same row as last cell[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Enter formula into "N5"[/TD]
[TD]=if(left($h4,3)="net",mid($h4,5,2)," ")[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Copy formula in N5:O5 to the bottom[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Enter formula into "O5"[/TD]
[TD]=IFERROR(IF(OR(L$1-H8>1000,L$1-H8<0)," ",L$1-H8)," ")[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Copy formula in O5:O5 to the bottom[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Enter template dys overdue categories Q3:U3[/TD]
[TD]Q3= "1 - 7 days"[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]R3= "8 - 14 days"[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]S3= "15 - 30 days"[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]T3= "31 - 60 days"[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]U3= "60+ days"[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Enter formulas in Q5:U5[/TD]
[TD]=IF($O12<8,$K12,"")[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]=IF(AND($O12>0,$O12<15),$K12,"")[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]=IF(AND($O12>14,$O12<31),$K12,"")[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]=IF(AND($O12>30,$O12<60),$K12,"")[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]=IF($O12>59,$K12,"")[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Copy Q5:U5 to the bottom[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Enter formula in Q1:V1[/TD]
[TD]=SUM(Q5:BOTTOM)/1000[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD]=SUM(R5:BOTTOM)/1000[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD]=SUM(S5:BOTTOM)/1000[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD]=SUM(T5:BOTTOM)/1000[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD]=SUM(U5:BOTTOM)/1000[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD][/TD]
[TD] =SUM(Q1:U1) [/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Format Q1:U1[/TD]
[TD] Yellow fill & Calibri 12 font & Borders all [/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]Format Z2:Z6 and AB2:AB6[/TD]
[TD] Yellow fill & Calibri 12 font & Borders all [/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Copy Q1:U1 of current workbook[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]open [/TD]
[TD]H:\Accounting\Over\Accounting\Month End Financial Close\2019 Month End Close\May\Overdue reporting template[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]**********************[/TD]
[TD]***How to name a file when folder name changes for each month?[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Paste Special in D8 of newly opened workbook[/TD]
[TD]Paste Special (value and number format & Transpose) into cell D8 of newly opened workbook[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]copy ab2:ab6 of "Aging"[/TD]
[TD]Paste Special (value and number format) into cell a16 of newly opened workbook[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Copy Z2:Z6 of current workbook[/TD]
[TD]Paste Special (value and number format) into cell a16 of newly opened workbook[/TD]
[/TR]
</tbody>[/TABLE]
36. Save workbook as H:\Accounting\Over\Accounting\Month End Financial Close\2019 Month End Close"AR Aging-AR Template.xlsx"
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 825"]
<tbody>[TR]
[TD]H:\Accounting\Over\Accounting\Month End Financial Close\2019 Month End Close\May\Overdue reporting template[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Marcelo Branco]

About this kind of formula
=LOOKUP(2,1/((condition1)*(condition2)),result_vector)

See the excellent explanation provided by Aladin Akyurek at
https://www.mrexcel.com/forum/excel...alue-unsorted-data-post492425.html#post492425

Hope this helps

M.

 

[dstepan]

I have a follow up question to Marcelo's response on 6/12/19 11:56 am.

The formulas and setup are working great. However, there are 3 customers that should be overlooked when trying to find the "Top 5 Customers".

These customers are Gmit, BGS GRM, and Gmit SC.

I'm not sure how to incorporate them into the formulas.

I thank you for your help.

 

[Marcelo Branco]

Sorry for the late reply - i hadn't seen your new question

Maybe something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

V​

[/TD]
[TD="bgcolor: #DCE6F1"]

W​

[/TD]
[TD="bgcolor: #DCE6F1"]

X​

[/TD]
[TD="bgcolor: #DCE6F1"]

Y​

[/TD]
[TD="bgcolor: #DCE6F1"]

Z​

[/TD]
[TD="bgcolor: #DCE6F1"]

AA​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Exclude​

[/TD]
[TD]

Large​

[/TD]
[TD]

Value​

[/TD]
[TD]

Customer​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Gmit​

[/TD]
[TD]

1​

[/TD]
[TD]

388000,00​

[/TD]
[TD]

DURG​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

BG5​

[/TD]
[TD]

2​

[/TD]
[TD]

324711,00​

[/TD]
[TD]

GNP​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

GRM​

[/TD]
[TD]

3​

[/TD]
[TD]

291518,00​

[/TD]
[TD]

CONSU​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Gmit SC​

[/TD]
[TD]

4​

[/TD]
[TD]

290946,70​

[/TD]
[TD]

FLYBY​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD]

170735,55​

[/TD]
[TD]

COVAN​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

324711​

[/TD]
[TD]

GNP​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

56002,5​

[/TD]
[TD]

AMERICA​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

75489​

[/TD]
[TD]

TONAN​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

290946,7​

[/TD]
[TD]

FLYBY​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

27​

[/TD]
[TD]

114986,5​

[/TD]
[TD]

BASIC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

28​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

29​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

30​

[/TD]
[TD]

5446,23​

[/TD]
[TD]

BAY​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

31​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

32​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

33​

[/TD]
[TD]

160449,3​

[/TD]
[TD]

BOW​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

34​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

35​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

36​

[/TD]
[TD]

26250​

[/TD]
[TD]

SOL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

37​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

38​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

39​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

40​

[/TD]
[TD]

291518​

[/TD]
[TD]

CONSU​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

41​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

42​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

43​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

44​

[/TD]
[TD]

170735,6​

[/TD]
[TD]

COVAN​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

45​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

46​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

47​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

48​

[/TD]
[TD]

388000​

[/TD]
[TD]

DURG​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

49​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in V4 copied down
=IF(ISNUMBER(C4),C4,"")

Formula in W4 copied down
=IF(ISNUMBER(V4),LOOKUP(2,1/((A$4:A4<>"INV")*(A$4:A4<>"Customer Totals:")),A$4:A4),"")

Exclusion list in X2:X5

Formula in Z2 copied down
=AGGREGATE(14,6,V$4:V$1000/ISNA(MATCH(W$4:W$1000,X$2:X$5,0)),Y2)

Formula in AA2 copied down
=INDEX(W$4:W$1000,MATCH(Z2,V$4:V$1000,0))

Hope this helps

M.

 

[dstepan]

I appreciate the help as this has been a challenge and definitely a learning experience for me.

I could use some help in converting the following formulas as I am having issues with reference style R1C1

Formula in W4 copied down
=IF(ISNUMBER(V4),LOOKUP(2,1/((A$4:A4<>"INV")*(A$4:A4<>"Customer Totals:")),A$4:A4),"")

Formula in Z2 copied down
=AGGREGATE(14,6,V$4:V$1000/ISNA(MATCH(W$4:W$1000,X$2:X$5,0)),Y2)

Formula in AA2 copied down
=INDEX(W$4:W$1000,MATCH(Z2,V$4:V$1000,0))



When I first entered the revised formula for V4 in the code it came back with an error.
I revised the formula so it was in R1C1 reference style (the rest of the code uses R1C1 vs A1).

Range("V4").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[-20]),RC[-20],0)"
It worked.

Therefore, I figured I would have to convert the rest of your formulas in the same format.
I tried to do so with the formula in Col W and got this error.

Compile error: Expected:end of statement

Here is what I tried:[TABLE="width: 906"]
<colgroup><col></colgroup><tbody>[TR]
[TD] Range("W4").Select[/TD]
[/TR]
[TR]
[TD] ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC-1),LOOKUP(2,1/((R$C-22:RC-22<>"INV")*(R$C-22:RC-22<>"Customer Totals:")),R$C-22:RC-22),"")"[/TD]
[/TR]
</tbody>[/TABLE]


Not sure what I am doing wrong.

Thank you!!!!!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

Why are you using R1C1-style? I think it's complicated and not intuitive

Why not?
Range("W4').Formula= "=......"

M.

 

[dstepan]

I am working at a temporary office and my computer was originally setup that way. It is a pretty long code. I finally received approval to change my options and so I changed it to A1 reference style. I tried adding the code the way you have it written but it didn't work out.
I think I will have to go back in and change my options back to the R1C1 reference style so your code will be translated.

I'm going to give it a shot now.

 

[dstepan]

Well I tried changing the reference style to R1C! and I am getting the following error:

Compile error: Syntax error

If I change the reference style back to A1 I get this error:

Compile error:
Expected:end of statement (it also highlights the INV in the formula)

Not sure what to do next.

Any ideas?