VBA help - Summarize report using dictionary approach

[Mallesh23]

Hi Team,

How to make 3 summarized report from Single Input file using dictionary Approach.

1) seperate account not opened.
2) Employee with 0 pay
3) Employee with Negative pay

Column A to F is my Input Data.

Expected Output.
Report 1) First Report Employee whose Bank not available
expected Output shown in Range h2:H5
Report 2) Second Report Employee With 0 Pay
expected Ouput shown in H9:K12
Report 3)Third Report Employee With -Negative amount
expected Ouput Range("H16:k19")

Below data with expected output.

Book15
	B	C	D	E	F	G	H	I	J	K
1	Employee Name	Account No	Joining Data	PrimaryBankName	Total Payble		First Report Employee whose Bank not available
2	Flintoff	100092	2011-03-23	SBI	10000		Sr No	Employee ID	Employee Name	PrimaryBankName
3	Gilchrist	100008	2013-01-15	HDFC	20000		1	100092	Dhoni	Account Not Opened
4	Sachin	100037	2013-01-15	ICICI	-5000		2	100023	Jaysurya	Account Not Opened
5	Dhoni	100092	2013-03-30	Account Not Opened	0		3	100070	Steve Waugh	Account Not Opened
6	Virat	100060	2013-05-27	SBI	0
7	Rohit	100032	2013-11-11	HDFC	0
8	Jaysurya	100023	2017-07-18	Account Not Opened	4000		Second Report Employee With 0 Pay
9	Ponting	100063	2019-06-22	SBI	50000		Sr No	Employee ID	Employee Name	Net Payble
10	Anderson	100098	2019-08-06	HDFC	-30000		1	100092	Dhoni	0
11	Steve Waugh	100070	2019-11-02	Account Not Opened	-4000		2	100060	Virat	0
12	Gayle	100042	2020-06-15	SBI	4000		3	100032	Rohit	0
13	Hardik	100087	2020-06-15	HDFC	50000
14	MacGrawth	100090	2020-06-15	SBI	40000
15							Third Report Employee With -Pay
16							Sr No	Employee ID	Employee Name	Net Payble
17							1	100037	Sachin	-5000
18							2	100098	Anderson	-30000
19							3	100070	Steve Waugh	-4000
Sheet1

Thanks
mg

 

[Mallesh23]

Hi Team,

Please Refer below Table, as I have missed Column A's Data Initially into Table.

How to make 3 summarized report from Single Input file using dictionary Approach.

Manually way it would have been be
Filtering Column E. Account not opened , Copy visible 4 columns. (bank ac no , emp id , emp name, net payble)
Filtering Column F. Amount is Negative , Copy visible 3 columns. (emp id , emp name, net payble)
Filtering Column F. Amount = 0, Copy visible 3 columns. (emp id , emp name, net payble)

output:-
1) account not opened.
2) Employee with 0 pay
3) Employee with Negative pay

Book15
	A	B	C	D	E	F	G	H	I	J	K	L
1	Employee ID	Employee Name	Bank Account No	Joining Data	PrimaryBankName	Total Payble		First Report Employee whose Bank not available
2	15774	Flintoff	100092	2011-03-23	SBI	10000		Sr No	Bank Account No	Employee ID	Employee Name	PrimaryBankName
3	19802	Gilchrist	100008	2013-01-15	HDFC	20000		1	100092	100092	Dhoni	Account Not Opened
4	13812	Sachin	100037	2013-01-15	ICICI	-5000		2	100023	100023	Jaysurya	Account Not Opened
5	19135	Dhoni	100092	2013-03-30	Account Not Opened	0		3	100070	100070	Steve Waugh	Account Not Opened
6	11383	Virat	100060	2013-05-27	SBI	0
7	12687	Rohit	100032	2013-11-11	HDFC	0
8	19189	Jaysurya	100023	2017-07-18	Account Not Opened	4000		Second Report Employee With 0 Pay
9	14665	Ponting	100063	2019-06-22	SBI	50000		Sr No	Employee ID	Employee Name	Net Payble
10	13461	Anderson	100098	2019-08-06	HDFC	-30000		1	100092	Dhoni	0
11	15502	Steve Waugh	100070	2019-11-02	Account Not Opened	-4000		2	100060	Virat	0
12	19340	Gayle	100042	2020-06-15	SBI	4000		3	100032	Rohit	0
13	18229	Hardik	100087	2020-06-15	HDFC	50000
14	15120	MacGrawth	100090	2020-06-15	SBI	40000
15								Third Report Employee With -Pay
16								Sr No	Employee ID	Employee Name	Net Payble
17								1	100037	Sachin	-5000
18								2	100098	Anderson	-30000
19								3	100070	Steve Waugh	-4000
Sheet1

Thanks
mg

 

[Mallesh23]

Hi Team ,

For above query , I want output in Seperate sheets. looking answer in dictionary or array method.

for example purpose kept data and output in single sheet.

Thanks in advance for your help.


Thanks
mg