LOOKUP IN VBA ARRAY AND DICTIONARY

[roykana]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	No Transaction	Date	Dept.	Code Pel.	Name Customer	Address	No.	Cd. Item	Name Item	Qty	Unit	Price	Pot. %	Total	Pot. :	Tax :	Costs :	Total End :	ITEM NO	LOOKUP LAST TRANS	LOOKUP BASED DATE TRANS
2	0002/KSR/TK/1221	09/12/2021		GENERAL	GENERAL		1	410288	TAMICI R 410288 RC/L-TOP	1	PCS	20,000	0	20,000	10,000	0	0	10,000	01-41810288	63200	40000
3	0002/KSR/TK/1221	09/12/2021		GENERAL	GENERAL		2	829740	TAMICI R 829740 RC/L-TOP	2	PCS	30,000	0	60,000				60,000	01-41610103	57600	48000
4	0002/KSR/TK/1221	09/12/2021		GENERAL	GENERAL		3	410240	TAMICI R 410240 RC/L-TOP	1	PCS	10,000	0	10,000				10,000	01-41710240	62400	44000
5	0002/KSR/TK/1221	09/12/2021		GENERAL	GENERAL		4	56117	ALFON P 56117 D1680 TG	1	PCS	25,000	0	25,000				25,000	01-11665117	57200	52000
6	0002/KSR/TK/1221	09/12/2021		GENERAL	GENERAL		5	222445	TAMICI R 222445 RC/L-TOP/USB	1	PCS	30,000	0	30,000				30,000	01-41910407	60000	32000
7	0002/KSR/TK/1221	09/12/2021		GENERAL	GENERAL		6	111195	TAMICI R 111195 RC/L-TOP/USB	1	PCS	15,000	0	15,000				15,000	01-42010434	54400	24000
8	0003/KSR/TK/1221	09/12/2021		GENERAL	GENERAL		1	2019	ALFON TP 2019 B	1	PCS	10,000	0	10,000	0	0	0	10,000	01-11524019	67000	20000
9	0004/KSR/TK/1221	09/12/2021		GENERAL	GENERAL		1	S305	SPLUSH R 305 P. RANGERS	1	PCS	35,000	0	35,000	0	0	0	35,000	01-13060011	78000	10000
MASTER

Cell Formulas
Range	Formula
T2:T9	T2	=LOOKUP(9^9,Table5[DATE]/(Table5[ITEM NO]=[@[ITEM NO]]),Table5[HB NET])
U2:U9	U2	=LOOKUP([@Date]+0.5,Table5[DATE]/(Table5[ITEM NO]=[@[ITEM NO]]),Table5[HB NET])

SOURCE

LOOKUP IN VBA ARRAY AND DICTIONARY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	PNM	ITM	QTY	CIU	NOD	DPR	DEPT	DATE	SAC	DIS%	GROUP DATA	REF CAMUS	TOTAL2	CUSTOMER NAME	ITEM NO	%dis	HB NET	ITEMDISCPC	year	MONTH&YEAR
2	100001	ALFON TP 2019 B	3	20000	58	0	BOJ	08/12/2021	A.03.01.018.032	0	SALES	UN-001	60000	UN-001	01-11524019	0	20000
3	100002	SPLUSH R 305 P. RANGERS	3	10000	68	0	BOJ	08/12/2021	A.03.01.018.032	0	SALES	UN-002	30000	UN-002	01-13060011	0	10000
4	100003	TAMICI RSL 111195 RC/L-TOP/USB	3	30000	02	0	BOJ	09/12/2021	A.03.01.018.032	20	SALES	UN-003	72000	UN-003	01-42010434	20	24000
5	100004	TAMICI R 222445 RC/L-TOP/USB	3	40000	18	0	BOJ	09/12/2021	A.03.01.018.032	20	SALES	UN-004	96000	UN-004	01-41910407	20	32000
6	100005	TAMICI R 410240 RC/L-TOP	3	55000	13	0	BOJ	09/12/2021	A.03.01.018.032	20	SALES	UN-005	132000	UN-005	01-41710240	20	44000
7	100006	TAMICI R 410288 RC/L-TOP	4	50000	16	0	BOJ	09/12/2021	A.03.01.018.032	20	SALES	UN-006	160000	UN-006	01-41810288	20	40000
8	100007	TAMICI R 829740 RC/L-TOP	3	60000	29	0	BOJ	09/12/2021	A.03.01.018.032	20	SALES	UN-007	144000	UN-007	01-41610103	20	48000
9	100008	ALFON P 56117 D1680 TG	3	65000	14	20	BOJ	09/12/2021	A.03.01.018.032	0	SALES	UN-008	156000	UN-008	01-11665117	0	52000
10	100009	ALFON TP 2019 B	3	67000	58	0	BOJ	10/12/2021	A.03.01.018.032	0	SALES	UN-009	201000	UN-009	01-11524019	0	67000
11	100010	SPLUSH R 305 P. RANGERS	3	78000	68	0	BOJ	10/12/2021	A.03.01.018.032	0	SALES	UN-010	234000	UN-010	01-13060011	0	78000
12	100011	TAMICI RSL 111195 RC/L-TOP/USB	3	68000	02	0	BOJ	10/12/2021	A.03.01.018.032	20	SALES	UN-011	163200	UN-011	01-42010434	20	54400
13	100012	TAMICI R 222445 RC/L-TOP/USB	3	75000	18	0	BOJ	10/12/2021	A.03.01.018.032	20	SALES	UN-012	180000	UN-012	01-41910407	20	60000
14	100013	TAMICI R 410240 RC/L-TOP	3	78000	13	0	BOJ	10/12/2021	A.03.01.018.032	20	SALES	UN-013	187200	UN-013	01-41710240	20	62400
15	100014	TAMICI R 410288 RC/L-TOP	4	79000	16	0	BOJ	11/12/2021	A.03.01.018.032	20	SALES	UN-014	252800	UN-014	01-41810288	20	63200
16	100015	TAMICI R 829740 RC/L-TOP	3	72000	29	0	BOJ	11/12/2021	A.03.01.018.032	20	SALES	UN-015	172800	UN-015	01-41610103	20	57600
17	100016	ALFON P 56117 D1680 TG	3	71500	14	20	BOJ	11/12/2021	A.03.01.018.032	0	SALES	UN-016	171600	UN-016	01-11665117	0	57200
DB SALES

Cell Formulas
Range	Formula
M2:M17	M2	=[@[HB NET]]*[@QTY]
Q2:Q17	Q2	=[@CIU]*(1-[@DPR]/100)*(1-[@[%dis]]/100)

 

[GlennUK]

Faster than what? Exactly how slow it is?

 

[roykana]

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

 

[GlennUK]

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.

 

[roykana]

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

 

[GlennUK]

@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.

 

[roykana]

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

 

[GlennUK]

@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.