+ Reply to Thread
Results 1 to 10 of 10

How to transpose and concatenate rows with similar first columns?

  1. #1
    Registered User
    Join Date
    11-06-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    57

    How to transpose and concatenate rows with similar first columns?

    My Excel sheet is formatted like this.

    lol.png

    Name Nicknames
    A Alex
    A A2D2
    A Bobsicle
    B Ballista
    B Pants
    C Cory
    D David
    D Daniel
    D Domson


    To

    Name Nicknames
    A Alex, A2D2, Bobsicle
    B Ballista, Pants
    C Cory
    D David, Daniel, Domson

    I want to transpose and concatenate the data with similar column A.

    Any help would be greatly appreciated!

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How to transpose and concatenate rows with similar first columns?

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: How to transpose and concatenate rows with similar first columns?

    same idea, little difference, everything is within the dictionary.
    perhaps adding "dict.CompareMode = vbTextCompare" for non case sensitive
    Please Login or Register  to view this content.
    Last edited by bsalv; 10-12-2021 at 03:16 AM.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How to transpose and concatenate rows with similar first columns?

    If just using Dict then
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: How to transpose and concatenate rows with similar first columns?

    OK, nice solution !
    Transpose is no problem here, but sometimes with more then 16.000 items is tricky.
    I had problems with that in the past, that's why I add an array with 2 elements as item.
    Last edited by bsalv; 10-12-2021 at 08:00 AM.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: How to transpose and concatenate rows with similar first columns?

    If you have Power Pivot or Power Query available, it's pretty simple to achieve that result.
    Rory

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: How to transpose and concatenate rows with similar first columns?


    Hi,

    the TRANSPOSE worksheet function limitation seems to be 65 536 elements
    and according to Excel basics it can be achieved without using a Dictionary …

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How to transpose and concatenate rows with similar first columns?

    Another option with formulas I suppose...
    Please Login or Register  to view this content.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to transpose and concatenate rows with similar first columns?

    Here is a simple tutorial on how to do this quickly in Power Pivot

    https://sfmagazine.com/post-entry/ju...a-pivot-table/
    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

  10. #10
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: How to transpose and concatenate rows with similar first columns?

    A few steps in power query
    Attached Files Attached Files

+ 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 transpose a single column into individual rows based on a similar data?
    By sushiant in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-13-2020, 03:51 AM
  2. [SOLVED] Macro to transpose rows into columns and paste to specific columns in another sheet
    By LDLDLD in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-05-2019, 11:08 AM
  3. Transpose Columns into Rows based on Unique Identifier for n columns
    By bhavt010785 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-01-2017, 11:55 AM
  4. [SOLVED] concatenate 3 rows ,and transpose it into single column in another sheet
    By raj soni in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2016, 06:33 AM
  5. [SOLVED] Formula to transpose rows to new columns and columns to new rows in Excel
    By bjnockle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-26-2014, 06:28 PM
  6. Replies: 1
    Last Post: 01-24-2014, 06:20 PM
  7. VBA Macro to Transpose Columns to Rows AND Concatenate Afterwards
    By ljanak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2013, 03:16 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