VBA code to sumifs and split across Business Case and Managers

Balajibenz

Board Regular
Joined
Nov 18, 2020
Messages
80
Office Version
  1. 2013
Platform
  1. Windows
Hi Can someone help me with below requirement.

I have base data in sheet1 with Business Case ID's and Managers etc as below. X to AI columns will have Jan to Dec data. I am only allowed to present some sample data due to confidentiality.


XYAIAJAMAN
1-Jan​
2-Feb​
12-Dec​
Yes/NoManagerBusiness Case
43​
27​
0​
YesRobert
3124​
11​
21​
14​
YesAmit
3124​
122​
140​
63​
NoRobert
238​
0​
39​
27​
YesAmit
238​

In sheet2 i am looking for the following view where for each manager there will be some Business Cases listed under them and for the each business case and for the each month data it should be displayed for Yes and No from column AJ from sheet1.

Manager
RobertJan'22Feb'22
YesNoYesNo
3124Hrs430270
%100%0%100%0%
238Hrs0122140
%0%100%0%100%
AmitJan'22Feb'22
YesNoYesNo
3124Hrs1121
%100%0%100%0%
238Hrs039
%#DIV/0!#DIV/0!100%0%


Manager data cell and Business case cell are are respectively merged with the empty cells right below them.

The original data is huge and in sheet2 each manager will have around 20 business cases under them so for each business case code should bring up Yes and No split for each month from Jan to Dec. it is working with sumifs formula but it is taking a lot of time and i am trying to automate it to reduce the time.

Thank you in advance and it would be of great help if someone help mw on this.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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