+ Reply to Thread
Results 1 to 7 of 7

Dynamically identify headers in a Pivot Table and procedurally CONCAT content for XLOOKUP

  1. #1
    Registered User
    Join Date
    04-08-2022
    Location
    Charlotte, NC, USA
    MS-Off Ver
    365
    Posts
    4

    Question Dynamically identify headers in a Pivot Table and procedurally CONCAT content for XLOOKUP

    I have written VBA to take one .csv file saved as an .xlsm that conditions data in the first sheet, creates a new sheet and Pivot Table, opens another locally saved .csv, conditions its data, and creates a new sheet and Pivot Table.
    4 sheets in total with 2 having conditioned raw data and two Pivot Tables created using said data.
    On the sheet entitled "PIVOT_GAP" I have CONCAT formulae to combine the sub-header in the Pivot Table and the following 1, 2, or 3 rows of associated data.
    After the data there is another sub-header and its data and repeats until the end of the Pivot Table which will never have the same rows created nor will they be in the same order.
    Currently, I manually copy + paste the CONCAT formulae that use the following system:
    Please Login or Register  to view this content.
    Where A4 is the sub-header and A5 is the data. For sub-headers with more than one row, each subsequent row would still reference the sub-header but use its row number as the new value:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Each row of associated data will always start with "PRESS" -
    Please Login or Register  to view this content.
    could be used as a check.
    What I need is to dynamically add formulae that will always CONCAT the sub-header with one row of associated data in a loop, move to the next row, CONCAT the same sub-header with the new row's data, and repeat.
    Every time a new sub-header is reached a new CONCAT formula is started with the appropriate "A"& row#. It will do this until the last row is reach.

    I'd perform the same process on the other Pivot Table sheet and then XLOOKUP for matching values before finishing the script.
    The code I wrote that is close but incorrect is as follows (part of a larger script):
    Please Login or Register  to view this content.
    PIVOT_GAPS.png
    I attempted to attach an example workbook but I am uncertain if it worked.
    Thank you in advance for your assistance!
    -Mark
    Attached Files Attached Files
    Last edited by sivispacem; 01-19-2024 at 04:42 PM. Reason: TYPOS

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

    Re: Dynamically identify headers in a Pivot Table and procedurally CONCAT content for XLOO

    Your Pivot table does not show the source data that may be necessary to work your issue. Once we see the source data, it may be possible to resolve your issue in Power Pivot to concatenate Text into a single cell with a DAX measure.
    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-08-2022
    Location
    Charlotte, NC, USA
    MS-Off Ver
    365
    Posts
    4

    Re: Dynamically identify headers in a Pivot Table and procedurally CONCAT content for XLOO

    I updated the attached xlsm. This now contains all 4 sheets with full data. It also has all of the original code less the new code I wrote in the post.
    (it is still the manual option described above)

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 VersionVersion 2411 Win 11
    Posts
    24,409

    Re: Dynamically identify headers in a Pivot Table and procedurally CONCAT content for XLOO

    In your source data, concatenate the two columns and then in your pivot, use the Immediate Owner and the Concatenated Column as your two Rows.

  5. #5
    Registered User
    Join Date
    04-08-2022
    Location
    Charlotte, NC, USA
    MS-Off Ver
    365
    Posts
    4

    Re: Dynamically identify headers in a Pivot Table and procedurally CONCAT content for XLOO

    Sometimes all it takes is a fresh pair of eyes... doesn't it?

    For the sake of learning, is there a way to do what I asked in the OP via VBA? Assuming, perhaps that I am given only two Pivot Table and no source data?

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 VersionVersion 2411 Win 11
    Posts
    24,409

    Re: Dynamically identify headers in a Pivot Table and procedurally CONCAT content for XLOO

    We need to wait for a VBA guru to jump in here. Not my forte.

  7. #7
    Registered User
    Join Date
    04-08-2022
    Location
    Charlotte, NC, USA
    MS-Off Ver
    365
    Posts
    4

    Re: Dynamically identify headers in a Pivot Table and procedurally CONCAT content for XLOO

    Thank you for your assistance regardless.

    I will hope a VBA guru happens by this post as I think this has to be something others have attempted.

+ 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] help using an Xlookup to pull headers from cells containing certain text into 1 cell
    By Earl38 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-13-2022, 02:14 PM
  2. Extracting data by Xlookup dynamically spill over two ranges
    By paradise2sr in forum Excel General
    Replies: 2
    Last Post: 10-29-2021, 10:04 AM
  3. [SOLVED] Copy Pivot into Table - - headers going into Table body
    By michellepace in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2016, 08:39 PM
  4. [SOLVED] Dynamically sort a pivot table
    By smls in forum Excel General
    Replies: 1
    Last Post: 08-11-2015, 04:57 PM
  5. Dynamically create headers in Excelsheet using Access table values
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-08-2014, 07:11 AM
  6. Identify table, Get Pivot, Copy the pivot table to new consolidated Sheet
    By akhileshgs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2013, 04:09 AM
  7. [SOLVED] Dynamically Changing Position of Pivot Based on Size of Another Pivot Table
    By ggilzow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2013, 02:42 PM

Tags for this Thread

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