Want To Change Formula To Vba Array & Dictionary

[roykana]

Dear All Master,
I want to change the formula to vba array and dictionary because there are 350000 records and I also use add custom columns in power query so excel formulas don't run automatically so my solution is to use vba.
for information my sheet name is "GSG" and my table name is "GSG_ALL"
Formula in column AD

=IF(AA2="SALES",IF(M2<=25000,M2+V2,0),"")

Formula in column AE

=IF(M2+P2+V2=0,"",IF(AA2="STOCK OPNAME","",IF(AA2="CHANGE ART","",IF(AA2="ITEM TRANSFER","",ROUND(IF(AA2="SALES",(M2+V2-AD2)/P2*100),1)))))

WANT TO CHANGE FORMULA TO VBA ARRAY & DICTIONARY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	CSB	GDN	PNM	DTS	DUD	SAC	OBY	ABY	DSC	PIN	PNP	PON	DIS	POS	SLS	BLC	PKP	PDO	PIV	CDD	RCV	DIS2	DEPT	OPT	TOTAL	NAME	GROUP DATA	REF CAMUS	CUSTOMER NAME	CASHDISCOUNT	%dis
2	FGD	0001	TEST12110001	01/10/2021	01/10/2021	10000	A			0	0	SONONE	200000	TRUE		1000000	FALSE			0	FALSE	40000	BOJ	0	1000000	A	SALES	STORE	A	0	24.00
3	FGD	0001	TEST12110002	02/10/2021	02/10/2021	10001	A			0	0	SONONE	30000	TRUE		200000	FALSE			0	FALSE	29400	BOJ	0	200000	B	SALES	STORE	B	0	29.70
4	FGD	0001	TEST12110003	03/10/2021	03/10/2021	10002	A			0	0	SONONE	45000	TRUE		300000	FALSE			0	FALSE	0	BOJ	0	300000	C	SALES	STORE	C	0	15.00
5	FGD	0001	TEST12110004	04/10/2021	04/10/2021	10003	A			0	0	SONONE	35000	TRUE		500000	FALSE			0	FALSE	0	BOJ	0	500000	D	SALES	STORE	D	0	7.00
6	FGD	0001	TEST12110005	05/10/2021	05/10/2021	10004	A			0	0	SONONE	25000	TRUE		250000	FALSE		1	0	FALSE	0	BOJ	0	250000	E	SALES	STORE	E	25000	0.00
7	FGD	0001	TEST12110006	05/10/2021	05/10/2021	10005	A			0	0	SONONE		TRUE		100000	FALSE			0	FALSE	0	BOJ	0	100000	F	ITEM TRANSFER	STORE	F
8	FGD	0001	TEST12110007	05/10/2021	05/10/2021	10006	A			0	0	SONONE		TRUE		20000	FALSE			0	FALSE	0	BOJ	0	20000	G	STOCK OPNAME	STORE	G
9	FGD	0001	TEST12110008	05/10/2021	05/10/2021	10007	A			0	0	SONONE		TRUE		20000	FALSE			0	FALSE	0	BOJ	0	20000	H	CHANGE ART	STORE	H
GSG

Cell Formulas
Range	Formula
AD2:AD9	AD2	=IF(AA2="SALES",IF(M2<=25000,M2+V2,0),"")
AE2:AE9	AE2	=IF(M2+P2+V2=0,"",IF(AA2="STOCK OPNAME","",IF(AA2="CHANGE ART","",IF(AA2="ITEM TRANSFER","",ROUND(IF(AA2="SALES",(M2+V2-AD2)/P2*100),1)))))

Thanks
roykana

 

[JGordon11]

You can add the last two columns using Power Query. The code below adds the columns, as described, to your table (starting without columns AD and AE):

let
    Source = Excel.CurrentWorkbook(){[Name="GSG_ALL"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"CSB", type text}, {"GDN", Int64.Type}, {"PNM", type text}, {"DTS", Int64.Type}, {"DUD", Int64.Type}, {"SAC", Int64.Type}, {"OBY", type text}, {"ABY", type any}, {"DSC", type any}, {"PIN", Int64.Type}, {"PNP", Int64.Type}, {"PON", type text}, {"DIS", Int64.Type}, {"POS", type logical}, {"SLS", type any}, {"BLC", Int64.Type}, {"PKP", type logical}, {"PDO", type any}, {"PIV", Int64.Type}, {"CDD", Int64.Type}, {"RCV", type logical}, {"DIS2", Int64.Type}, {"DEPT", type text}, {"OPT", Int64.Type}, {"TOTAL", Int64.Type}, {"NAME", type text}, {"GROUP DATA", type text}, {"REF CAMUS", type text}, {"CUSTOMER NAME", type text}}),
    tbl1 = Table.AddColumn(Type,"CASHDISCOUNT", each 
        if [GROUP DATA] = "SALES" then if [DIS] <= 25000 then [DIS] + [DIS2] else 0 else null),
    tbl2 = Table.AddColumn(tbl1,"%dis", each 
        if [DIS] + [BLC] +[DIS2] = 0 then null else
            if [GROUP DATA] = "STOCK OPNAME" then null else 
                if [GROUP DATA] = "CHANGE ART" then null else
                    if [GROUP DATA] = "ITEM TRANSFER" then null else
                        Number.Round(if [GROUP DATA] ="SALES" then ([DIS] + [DIS2] - [CASHDISCOUNT])*100/[BLC] else 1,2))
in
    tbl2

 

[roykana]

You can add the last two columns using Power Query. The code below adds the columns, as described, to your table (starting without columns AD and AE):

let
    Source = Excel.CurrentWorkbook(){[Name="GSG_ALL"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"CSB", type text}, {"GDN", Int64.Type}, {"PNM", type text}, {"DTS", Int64.Type}, {"DUD", Int64.Type}, {"SAC", Int64.Type}, {"OBY", type text}, {"ABY", type any}, {"DSC", type any}, {"PIN", Int64.Type}, {"PNP", Int64.Type}, {"PON", type text}, {"DIS", Int64.Type}, {"POS", type logical}, {"SLS", type any}, {"BLC", Int64.Type}, {"PKP", type logical}, {"PDO", type any}, {"PIV", Int64.Type}, {"CDD", Int64.Type}, {"RCV", type logical}, {"DIS2", Int64.Type}, {"DEPT", type text}, {"OPT", Int64.Type}, {"TOTAL", Int64.Type}, {"NAME", type text}, {"GROUP DATA", type text}, {"REF CAMUS", type text}, {"CUSTOMER NAME", type text}}),
    tbl1 = Table.AddColumn(Type,"CASHDISCOUNT", each
        if [GROUP DATA] = "SALES" then if [DIS] <= 25000 then [DIS] + [DIS2] else 0 else null),
    tbl2 = Table.AddColumn(tbl1,"%dis", each
        if [DIS] + [BLC] +[DIS2] = 0 then null else
            if [GROUP DATA] = "STOCK OPNAME" then null else
                if [GROUP DATA] = "CHANGE ART" then null else
                    if [GROUP DATA] = "ITEM TRANSFER" then null else
                        Number.Round(if [GROUP DATA] ="SALES" then ([DIS] + [DIS2] - [CASHDISCOUNT])*100/[BLC] else 1,2))
in
    tbl2

Dear Mr.JGordon11

thank you very much and your power query code works perfectly.

Thanks
roykana