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

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
[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]
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!!!!!
 
Upvote 0
You are welcome. Thanks for the feedback.

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

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

M.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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