LOOKUP IN VBA ARRAY AND DICTIONARY

roykana

Active Member
Joined
Mar 8, 2018
Messages
311
Office Version
  1. 2010
Platform
  1. Windows
Dear All Master,

I want the formula in the T & U column to be a vba array and dictonary code

so that it becomes faster and please also add a comment matrix in the code so it's easy to understand.


Thanks
roykana


DESIRED RESULT
LOOKUP IN VBA ARRAY AND DICTIONARY.xlsm
ABCDEFGHIJKLMNOPQRSTU
1No TransactionDateDept.Code Pel.Name CustomerAddressNo.Cd. ItemName ItemQtyUnitPricePot. %TotalPot. :Tax :Costs :Total End :ITEM NOLOOKUP LAST TRANSLOOKUP BASED DATE TRANS
20002/KSR/TK/122109/12/2021GENERALGENERAL1410288TAMICI R 410288 RC/L-TOP1PCS20,000020,00010,0000010,00001-418102886320040000
30002/KSR/TK/122109/12/2021GENERALGENERAL2829740TAMICI R 829740 RC/L-TOP2PCS30,000060,00060,00001-416101035760048000
40002/KSR/TK/122109/12/2021GENERALGENERAL3410240TAMICI R 410240 RC/L-TOP1PCS10,000010,00010,00001-417102406240044000
50002/KSR/TK/122109/12/2021GENERALGENERAL456117ALFON P 56117 D1680 TG1PCS25,000025,00025,00001-116651175720052000
60002/KSR/TK/122109/12/2021GENERALGENERAL5222445TAMICI R 222445 RC/L-TOP/USB1PCS30,000030,00030,00001-419104076000032000
70002/KSR/TK/122109/12/2021GENERALGENERAL6111195TAMICI R 111195 RC/L-TOP/USB1PCS15,000015,00015,00001-420104345440024000
80003/KSR/TK/122109/12/2021GENERALGENERAL12019ALFON TP 2019 B1PCS10,000010,00000010,00001-115240196700020000
90004/KSR/TK/122109/12/2021GENERALGENERAL1S305SPLUSH R 305 P. RANGERS1PCS35,000035,00000035,00001-130600117800010000
MASTER
Cell Formulas
RangeFormula
T2:T9T2=LOOKUP(9^9,Table5[DATE]/(Table5[ITEM NO]=[@[ITEM NO]]),Table5[HB NET])
U2:U9U2=LOOKUP([@Date]+0.5,Table5[DATE]/(Table5[ITEM NO]=[@[ITEM NO]]),Table5[HB NET])



SOURCE
LOOKUP IN VBA ARRAY AND DICTIONARY.xlsm
ABCDEFGHIJKLMNOPQRST
1PNMITMQTYCIUNODDPRDEPTDATESACDIS%GROUP DATAREF CAMUSTOTAL2CUSTOMER NAMEITEM NO%disHB NETITEMDISCPCyearMONTH&YEAR
2100001ALFON TP 2019 B320000580BOJ08/12/2021A.03.01.018.0320SALESUN-00160000UN-00101-11524019020000
3100002SPLUSH R 305 P. RANGERS310000680BOJ08/12/2021A.03.01.018.0320SALESUN-00230000UN-00201-13060011010000
4100003TAMICI RSL 111195 RC/L-TOP/USB330000020BOJ09/12/2021A.03.01.018.03220SALESUN-00372000UN-00301-420104342024000
5100004TAMICI R 222445 RC/L-TOP/USB340000180BOJ09/12/2021A.03.01.018.03220SALESUN-00496000UN-00401-419104072032000
6100005TAMICI R 410240 RC/L-TOP355000130BOJ09/12/2021A.03.01.018.03220SALESUN-005132000UN-00501-417102402044000
7100006TAMICI R 410288 RC/L-TOP450000160BOJ09/12/2021A.03.01.018.03220SALESUN-006160000UN-00601-418102882040000
8100007TAMICI R 829740 RC/L-TOP360000290BOJ09/12/2021A.03.01.018.03220SALESUN-007144000UN-00701-416101032048000
9100008ALFON P 56117 D1680 TG3650001420BOJ09/12/2021A.03.01.018.0320SALESUN-008156000UN-00801-11665117052000
10100009ALFON TP 2019 B367000580BOJ10/12/2021A.03.01.018.0320SALESUN-009201000UN-00901-11524019067000
11100010SPLUSH R 305 P. RANGERS378000680BOJ10/12/2021A.03.01.018.0320SALESUN-010234000UN-01001-13060011078000
12100011TAMICI RSL 111195 RC/L-TOP/USB368000020BOJ10/12/2021A.03.01.018.03220SALESUN-011163200UN-01101-420104342054400
13100012TAMICI R 222445 RC/L-TOP/USB375000180BOJ10/12/2021A.03.01.018.03220SALESUN-012180000UN-01201-419104072060000
14100013TAMICI R 410240 RC/L-TOP378000130BOJ10/12/2021A.03.01.018.03220SALESUN-013187200UN-01301-417102402062400
15100014TAMICI R 410288 RC/L-TOP479000160BOJ11/12/2021A.03.01.018.03220SALESUN-014252800UN-01401-418102882063200
16100015TAMICI R 829740 RC/L-TOP372000290BOJ11/12/2021A.03.01.018.03220SALESUN-015172800UN-01501-416101032057600
17100016ALFON P 56117 D1680 TG3715001420BOJ11/12/2021A.03.01.018.0320SALESUN-016171600UN-01601-11665117057200
DB SALES
Cell Formulas
RangeFormula
M2:M17M2=[@[HB NET]]*[@QTY]
Q2:Q17Q2=[@CIU]*(1-[@DPR]/100)*(1-[@[%dis]]/100)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Faster than what? Exactly how slow it is?
 
Upvote 0
Faster than what? Exactly how slow it is?
@GlennUK
faster than the lookup formula and the actual record is 100000 and please separate the vba code for the 2 lookup types.
very slow that's why I chose to use the vba array and dictionary code
 
Upvote 0
Taking a look at those formulas, I can't see how the logic can be applied any faster in VBA. Maybe someone disagrees and can provide a solution. Personally I would try to design logic for the data that prevented 100,000 cell array operations - like having a table of max date per ITEM NO to allow an intermediate step to build a direct key for matching to the relevant data for LOOKUP LAST TRANS.
 
Upvote 0
Taking a look at those formulas, I can't see how the logic can be applied any faster in VBA. Maybe someone disagrees and can provide a solution. Personally I would try to design logic for the data that prevented 100,000 cell array operations - like having a table of max date per ITEM NO to allow an intermediate step to build a direct key for matching to the relevant data for LOOKUP LAST TRANS.
@GlennUK

Thank you for your reply. Maybe it's better you can try first with your vba logic. I will always appreciate your efforts
 
Upvote 0
@GlennUK

Thank you for your reply. Maybe it's better you can try first with your vba logic. I will always appreciate your efforts
Maybe it is better that you design logic that prevents 100,000 cell-referencing array logic. Like having 3 PivotTables off your data, one for max date per ITEM NO, one for count distinct of dates per ITEM NO, one for list of dates per ITEM NO (design as tabular), and use those as either a direct lookup key, or to build a lookup key in stages.
 
Upvote 0
Maybe it is better that you design logic that prevents 100,000 cell-referencing array logic. Like having 3 PivotTables off your data, one for max date per ITEM NO, one for count distinct of dates per ITEM NO, one for list of dates per ITEM NO (design as tabular), and use those as either a direct lookup key, or to build a lookup key in stages.
@GlennUK
thanks for your reply, i just want to use vba array and dictionary. If you have vba code logic, we can try it first
 
Upvote 0
@GlennUK
thanks for your reply, i just want to use vba array and dictionary. If you have vba code logic, we can try it first
No, I do not have VBA for it - and I'm not going to write it just to prove it will be just as slow as formulas. Take my advice, and change your data logic, and have it work a thousand times faster.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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