+ Reply to Thread
Results 1 to 4 of 4

consolidating rows in one column

  1. #1
    Registered User
    Join Date
    01-30-2020
    Location
    toronto canada
    MS-Off Ver
    MS exchange
    Posts
    1

    consolidating rows in one column

    Here is my data

    External first name external last name relationship Internal name
    John Smith leader Sara Jones
    John Smith leader Jennifer Smith
    Bob Brown leader David Chan
    Michelle Adam leader Noel David
    Michelle Adam leader Allen Stevens
    Michelle Adam leader Jordan Wood
    Michelle Adam leader Erin May
    Michelle Adam leader Karen Sinclair

    And this is what i want my data to look like:

    External first name external last name relationship Internal name
    John Smith leader Sara Jones; Jennifer Smith
    Bob Brown leader David Chan
    Michelle Adam leader Noel David; Allen Stevens; Jordan Wood; Erin May; Karen Sinclair

    Is this possible without manually doing it? data is over 1000 rows long. Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: consolidating rows in one column

    Administrative note

    Hi and welcome
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks

  3. #3
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: consolidating rows in one column

    I went about this is a different way, Maybe it will work for you. I have attached the worksheet, work has been done in the "IDEA" tab.
    I created a pivot table based on your excel question tab to get unique values quickly. In the pivot table options I removed Sub Totals. Also under the Pivot table Rows click the down arrow for each of the three items. Then select field Settings. Then select Layout and Print. Then make sure to select Show Item labels in tabular form. Make sure to do this for each of the three items.

    Once your Pivot table is accurate i created an array formula in column D. Array formulas are entered with CTRL+SHIFT+ENTER. If done so correctly { } are added around the formula.

    =TEXTJOIN("; ",TRUE,IF($A4&$B4='excel question'!$A$1:$A$1000&'excel question'!$B$1:$B$1000,'excel question'!$D$1:$D$1000,""))

    This formula returns your column D names from Excel Question tab based on first and last name in columns A and B. The Textjoin formula is a way to show all your results in one cell. Of course if you do not have Excel 2016 or 365 then you probably don't have the textjoin function, and this formula will not work for you.
    Attached Images Attached Images
    Attached Files Attached Files
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: consolidating rows in one column

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new, I have provided the links for you this time.)

    https://www.mrexcel.com/board/thread...olumn.1122441/
    https://www.excelguru.ca/forums/show...-in-one-column
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Replies: 3
    Last Post: 04-12-2019, 04:41 PM
  2. Convert rows to columns while consolidating Column A
    By noelg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2018, 02:54 PM
  3. Consolidating rows
    By bcochra in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-13-2016, 05:49 PM
  4. consolidating rows
    By jeck876 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-28-2015, 02:05 PM
  5. Need help consolidating rows
    By Vandy in forum Excel General
    Replies: 3
    Last Post: 08-09-2013, 12:21 AM
  6. Consolidating Rows
    By brcaston in forum Excel General
    Replies: 5
    Last Post: 06-24-2010, 01:31 PM
  7. Consolidating rows
    By Sir08 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-19-2008, 08:46 AM

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