+ Reply to Thread
Results 1 to 6 of 6

Split multi lines within in single cell into separate cells and copy other data to match

  1. 10-11-2021, 03:25 AM #1
    neilosj
    neilosj is offline
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Split multi lines within in single cell into separate cells and copy other data to match

    Hi Community,

    I have a sheet which consists of four columns and a few rows.
    A person is asked for requests, and these requests are always listed within a single cell.
    I need to get each requested item into a single cell and copy the data for each person so each row has all the information.

    This example is showing the info I have in the top table, and how I need it on the lower.
    My real data is much larger than this.

    Any help is appreceated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-11-2021, 07:56 AM #2
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Split multi lines within in single cell into separate cells and copy other data to mat

    Power Query

    Please Login or Register  to view this content.

    Formula

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  3. 10-11-2021, 08:21 AM #3
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,475

    Re: Split multi lines within in single cell into separate cells and copy other data to mat

    You can use a bit of VBA to remove the duplicates, then loop through the original data to find the corresponding data.

    The assumption is the names and dates will not always be the same and types
    Please Login or Register  to view this content.
    See attached sample
    Its a .xlsm workbook, macros will need to be enabled .
    Attached Files Attached Files
    ,
    Xlorate.com, Selection Codes, YouYube Channel
    Reply With QuoteRegister To Reply
  4. 10-11-2021, 09:13 AM #4
    neilosj
    neilosj is offline
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Re: Split multi lines within in single cell into separate cells and copy other data to mat

    @Bo_Ry The formula method works well as a quick copy pasta. As I will be taking the data out of a working sheet, manipulate the data as described in this thread and add it back in.

    The Power Query method, does what is needed too.
    Thanks
    Last edited by neilosj; 10-11-2021 at 09:15 AM. Reason: replied too soon
    Reply With QuoteRegister To Reply
  5. 10-11-2021, 09:15 AM #5
    AliGW
    AliGW is online now
    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
    80,421

    Re: Split multi lines within in single cell into separate cells and copy other data to mat

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    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 With QuoteRegister To Reply
  6. 10-11-2021, 09:17 AM #6
    neilosj
    neilosj is offline
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Re: Split multi lines within in single cell into separate cells and copy other data to mat

    @davesexcel You have inverted my question!
    I have the cells with multiple lines and I need them to be split out to individual cells.
    You have taken the individual cells and combined to a single cell.
    I'm sure that'll be of use too. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Line Graph - unable to split lines up to show separate data
    By chris.smith93 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-05-2021, 04:38 AM
  2. [SOLVED] Want to convert multiple lines of data in single cell to multiple cells with single lines
    By copperberry in forum Excel General
    Replies: 1
    Last Post: 05-06-2016, 08:22 PM
  3. List Data from Cells in a single Table Row into a multi-column and multi-row Range.
    By Asaan in forum Excel General
    Replies: 19
    Last Post: 10-05-2012, 01:03 PM
  4. [SOLVED] Copy content of several columns into a single cell and change format on separate lines
    By Biffilicious in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2012, 05:05 AM
  5. How do I split a single cell into separate rows copied down?
    By Nick8888 in forum Excel General
    Replies: 2
    Last Post: 09-24-2006, 08:33 AM
  6. Split data from one cell to two separate cells
    By Michele in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2005, 05:05 PM
  7. Creating single lines of data from a multi-column table
    By hywel in forum Excel General
    Replies: 3
    Last Post: 02-11-2005, 07:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1