+ Reply to Thread
Results 1 to 4 of 4

VBA to create crosstab/pivot table in new sheet

  1. #1
    Registered User
    Join Date
    04-14-2017
    Location
    california
    MS-Off Ver
    2013
    Posts
    19

    VBA to create crosstab/pivot table in new sheet

    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
    Last edited by ry94080; 01-10-2025 at 06:24 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,705

    Re: VBA to create crosstab/pivot table in new sheet

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-14-2017
    Location
    california
    MS-Off Ver
    2013
    Posts
    19

    Re: VBA to create crosstab/pivot table in new sheet

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

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,186

    Re: VBA to create crosstab/pivot table in new sheet

    These, copied down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] how to create Pivot Table using multiple sheet
    By emmr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-17-2021, 04:40 AM
  2. Replies: 16
    Last Post: 06-05-2020, 04:48 PM
  3. Crosstab table to pivot table to plain table exceeds Excel row limit
    By jjsilva in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-30-2017, 11:43 PM
  4. I want to CREATE TWO PIVOT table in one sheet
    By vinod2802 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2016, 03:32 AM
  5. How to create a macro to create a pivot table on a new sheet
    By thlee1122 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2015, 11:14 AM
  6. Replies: 1
    Last Post: 03-08-2011, 12:31 PM
  7. Excel Pivot Table Plugin? (crosstab to data table)
    By HoMoon115 in forum Excel General
    Replies: 0
    Last Post: 02-22-2006, 04:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1