VBA to create crosstab/pivot table in new sheet

[ry94080]

Hello,

I have the dataset below:

MRN NAME UNIT NUMBER PRODUCT CODE ORDER DATE START TIME END TIME PRODUCT STATUS ADMIN USER DUAL SIGNOFF USER DEPARTMENT
1 DATA DATA DATA DATA 1/1/2025 14:00 DATA DATA DATA A ICU
2 DATA DATA DATA DATA 1/1/2025 14:01 DATA DATA DATA B ICU
3 DATA DATA DATA DATA 1/1/2025 14:02 DATA DATA DATA C ED
4 DATA DATA DATA DATA 1/1/2025 14:03 1/2/2025 15:03 DATA DATA DATA ED
5 DATA DATA DATA DATA 1/1/2025 14:04 1/2/2025 15:04 DATA DATA DATA ED
6 DATA DATA DATA DATA 1/1/2025 14:05 1/2/2025 15:05 DATA DATA DATA ICU
7 DATA DATA DATA DATA 1/1/2025 14:06 1/2/2025 15:06 DATA DATA DATA D ICU
8 DATA DATA DATA DATA 1/2/2025 15:07 DATA DATA DATA E ICU
9 DATA DATA DATA DATA 1/2/2025 15:08 DATA DATA DATA F ICU
10 DATA DATA DATA DATA 1/1/2025 14:09 1/2/2025 15:09 DATA DATA DATA G ICU
11 DATA DATA DATA DATA 1/1/2025 14:10 1/2/2025 15:10 DATA DATA DATA H INF
12 DATA DATA DATA DATA 1/1/2025 14:11 1/2/2025 15:11 DATA DATA DATA I INF
13 DATA DATA DATA DATA 1/1/2025 14:12 1/2/2025 15:12 DATA DATA DATA J ED


I was hoping to setup a vba script that would create subtotals on columns START TIME, END TIME, and DUAL SIGNOFF USER that would count BLANKS. Grouped by the DEPT column.

Something like this:

START TIME END TIME DUAL SIGNOFF USER
ICU 2 2 1
ED 0 1 2
INF 0 0 0


Any ideas?example.xlsx

[alansidman]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed. Additionally, please mock up a solution for the sample you have provided

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[ry94080]

Ok thanks. I've added an example xlsx to my original post

[TMS]

These, copied down:

=COUNTIFS(Sheet1!$L$2:$L$14,$A2,Sheet1!F$2:F$14,"")

=COUNTIFS(Sheet1!$L$2:$L$14,$A2,Sheet1!G$2:G$14,"")

=COUNTIFS(Sheet1!$L$2:$L$14,$A2,Sheet1!K$2:K$14,"")