+ Reply to Thread
Results 1 to 2 of 2

Concatenate option in pivot

  1. #1
    Registered User
    Join Date
    11-28-2010
    Location
    India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    77

    Concatenate option in pivot

    Hi Guys,


    Please help me I need a help. Is it possible to concatenate in pivot table.

    Excel attached. In column I have maintained what I want.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    Re: Concatenate option in pivot

    @ sryadav12

    I do not know of a way to do this directly in Pivot tables. If it is acceptable to do the concatenation in the source table this seems to do what is described above.

    In Sheet1 of the attached find a column (D) labeled Concat. In that column find this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This requires a User Defined Function. This one was written by 'tigeravatar'. The code is below to be copied and pasted into a module in the VBA editor. The "Personal.xlsb!" prefix will need to be removed. That is there because that is where I store UDFs. This means that the file will present errors upon download. When adjustments are made the table in Sheet1 will look like this:

    Row\Col
    A
    B
    C
    D
    1
    SO
    PRO
    Ref
    Concat
    2
    SO080310
    PRO-126146
    100000529
    PRO-126146, PRO-126147
    3
    PRO-126147
    100000529
    4
    SO080311
    PRO-126148
    100000530
    PRO-126148
    5
    SO080312
    PRO-126149
    100000531
    PRO-126149
    6
    SO080313
    PRO-126150
    100000532
    PRO-126150
    7
    SO080314
    PRO-126151
    100000533
    PRO-126151
    8
    SO080315
    PRO-126152
    100000534
    PRO-126152
    9
    SO080316
    PRO-126153
    100000535
    PRO-126153
    10
    SO080317
    PRO-126154
    100000536
    PRO-126154
    11
    SO080318
    PRO-126155
    100000537
    PRO-126155
    12
    SO080319
    PRO-126156
    100000538
    PRO-126156, PRO-126157
    13
    PRO-126157
    100000538
    14
    SO080320
    PRO-126158
    100000539
    PRO-126158, PRO-126159, PRO-126160, PRO-126161, PRO-126162
    15
    PRO-126159
    100000539
    16
    PRO-126160
    100000539
    17
    PRO-126161
    100000539
    18
    PRO-126162
    100000539
    19
    SO080321
    PRO-126163
    100000540
    PRO-126163
    20
    SO080322
    PRO-126164
    100000541
    PRO-126164
    21
    SO080323
    PRO-126165
    300000316
    PRO-126165
    22
    SO080324
    PRO-126166
    300000318
    PRO-126166


    The UDF code is here:
    Please Login or Register  to view this content.
    Then change the data source for the Pivot table and update.

    The Pivot table looks like this:

    Row\Col
    A
    B
    C
    D
    3
    Ref PRO SO Concat
    4
    100000529
    PRO-126146 SO080310 PRO-126146, PRO-126147
    5
    PRO-126147 (blank)
    6
    100000530
    PRO-126148 SO080311 PRO-126148
    7
    100000531
    PRO-126149 SO080312 PRO-126149
    8
    100000532
    PRO-126150 SO080313 PRO-126150
    9
    100000533
    PRO-126151 SO080314 PRO-126151
    10
    100000534
    PRO-126152 SO080315 PRO-126152
    11
    100000535
    PRO-126153 SO080316 PRO-126153
    12
    100000536
    PRO-126154 SO080317 PRO-126154
    13
    100000537
    PRO-126155 SO080318 PRO-126155
    14
    100000538
    PRO-126156 SO080319 PRO-126156, PRO-126157
    15
    PRO-126157 (blank)
    16
    100000539
    PRO-126158 SO080320 PRO-126158, PRO-126159, PRO-126160, PRO-126161, PRO-126162
    17
    PRO-126159 (blank)
    18
    PRO-126160 (blank)
    19
    PRO-126161 (blank)
    20
    PRO-126162 (blank)
    21
    100000540
    PRO-126163 SO080321 PRO-126163
    22
    100000541
    PRO-126164 SO080322 PRO-126164
    23
    300000316
    PRO-126165 SO080323 PRO-126165
    24
    300000318
    PRO-126166 SO080324 PRO-126166
    25
    Grand Total
    26

    Does this do what you want.
    Attached Files Attached Files
    Last edited by FlameRetired; 08-10-2015 at 03:39 PM.
    Dave

+ 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. Concanate option in pivot
    By sryadav12 in forum Excel General
    Replies: 1
    Last Post: 08-10-2015, 07:26 AM
  2. Group option and calculated field option greyed out on pivot table
    By KevinMolina in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-16-2015, 05:00 PM
  3. Pivot table option
    By vinayjan in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 08-31-2013, 02:29 PM
  4. Collapse/Expand - Pivot table Fields - Need equivalent option in Excel VBA Pivot table
    By ragavendraph in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2012, 03:00 PM
  5. Concatenate option
    By nirola in forum Excel General
    Replies: 1
    Last Post: 06-29-2011, 11:22 AM
  6. Concatenate option button with combobox values
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-26-2010, 06:29 PM
  7. Replies: 2
    Last Post: 06-26-2007, 02:55 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