VBA help - Summarize report using dictionary approach

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
844
Office Version
  1. 2010
Platform
  1. Windows
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
BCDEFGHIJK
1Employee NameAccount NoJoining DataPrimaryBankNameTotal PaybleFirst Report Employee whose Bank not available
2Flintoff1000922011-03-23SBI10000Sr NoEmployee IDEmployee NamePrimaryBankName
3Gilchrist1000082013-01-15HDFC200001100092DhoniAccount Not Opened
4Sachin1000372013-01-15ICICI-50002100023JaysuryaAccount Not Opened
5Dhoni1000922013-03-30Account Not Opened03100070Steve WaughAccount Not Opened
6Virat1000602013-05-27SBI0
7Rohit1000322013-11-11HDFC0
8Jaysurya1000232017-07-18Account Not Opened4000Second Report Employee With 0 Pay
9Ponting1000632019-06-22SBI50000Sr NoEmployee IDEmployee NameNet Payble
10Anderson1000982019-08-06HDFC-300001100092Dhoni0
11Steve Waugh1000702019-11-02Account Not Opened-40002100060Virat0
12Gayle1000422020-06-15SBI40003100032Rohit0
13Hardik1000872020-06-15HDFC50000
14MacGrawth1000902020-06-15SBI40000
15Third Report Employee With -Pay
16Sr NoEmployee IDEmployee NameNet Payble
171100037Sachin-5000
182100098Anderson-30000
193100070Steve Waugh-4000
Sheet1


Thanks
mg
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
844
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKL
1Employee IDEmployee NameBank Account NoJoining DataPrimaryBankNameTotal PaybleFirst Report Employee whose Bank not available
215774Flintoff1000922011-03-23SBI10000Sr NoBank Account NoEmployee IDEmployee NamePrimaryBankName
319802Gilchrist1000082013-01-15HDFC200001100092100092DhoniAccount Not Opened
413812Sachin1000372013-01-15ICICI-50002100023100023JaysuryaAccount Not Opened
519135Dhoni1000922013-03-30Account Not Opened03100070100070Steve WaughAccount Not Opened
611383Virat1000602013-05-27SBI0
712687Rohit1000322013-11-11HDFC0
819189Jaysurya1000232017-07-18Account Not Opened4000Second Report Employee With 0 Pay
914665Ponting1000632019-06-22SBI50000Sr NoEmployee IDEmployee NameNet Payble
1013461Anderson1000982019-08-06HDFC-300001100092Dhoni0
1115502Steve Waugh1000702019-11-02Account Not Opened-40002100060Virat0
1219340Gayle1000422020-06-15SBI40003100032Rohit0
1318229Hardik1000872020-06-15HDFC50000
1415120MacGrawth1000902020-06-15SBI40000
15Third Report Employee With -Pay
16Sr NoEmployee IDEmployee NameNet Payble
171100037Sachin-5000
182100098Anderson-30000
193100070Steve Waugh-4000
Sheet1



Thanks
mg
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
844
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

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
Top