+ Reply to Thread
Results 1 to 7 of 7

Multi-Line Cells to Rows, retaining info from preceding column.

Hybrid View

  1. 05-03-2018, 02:53 PM #1
    DollarBankITAssetMgt
    DollarBankITAssetMgt is offline
    Registered User
    Join Date
    05-03-2018
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    2010
    Posts
    5

    Question Multi-Line Cells to Rows, retaining info from preceding column.

    Hi

    We are in the early stages of a Windows 10 migration and are looking at compatibility for all installed software within our organization, prioritizing by applications with the most frequency. Our software management tool can report software installations by computer name, but it's done in a multi-line cell for each computer. I need a way to convert that multi-line information into rows, retaining the computer name for each installation (example pictured). That will allow me to get an instance count for each different software item in use. I realize I could do this via text to columns then transposing columns to rows, but that's far too labor intensive since there are thousands of lines. I'm hoping someone here has experienced something similar and can help out.

    Thanks
    Chris

    Multi Lines to Rows.JPG
    Reply With QuoteRegister To Reply
  2. 05-03-2018, 03:02 PM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,372

    Re: Multi-Line Cells to Rows, retaining info from preceding column.

    Hi DollarBank.. and welcome to the forum,

    In a newer Excel tool called "Power Query" for 2010 or "Get & Transform" in 2016 your problem is a snap. You simply need to do a Fill Up process. You can install the Power Query Add-In for free from Microsoft at:

    https://www.microsoft.com/en-us/down...6-b0da1f54ed59

    Then use Fill Up.

    https://www.excelcampus.com/library/...s-power-query/
    Last edited by MarvinP; 05-03-2018 at 03:05 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 05-03-2018, 04:05 PM #3
    DollarBankITAssetMgt
    DollarBankITAssetMgt is offline
    Registered User
    Join Date
    05-03-2018
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    2010
    Posts
    5

    Re: Multi-Line Cells to Rows, retaining info from preceding column.

    Marvin,

    I don't think this is what I'm looking for. It solves the second part of my problem, but I need a way to split those multi-line cells into rows within the same column first. Note only 4 rows in my first list and 12 rows in my desired list. Any suggestion there?

    Thanks
    Chris
    Reply With QuoteRegister To Reply
  4. 05-03-2018, 04:05 PM #4
    DollarBankITAssetMgt
    DollarBankITAssetMgt is offline
    Registered User
    Join Date
    05-03-2018
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    2010
    Posts
    5

    Re: Multi-Line Cells to Rows, retaining info from preceding column.

    Hmm... Not working.
    Reply With QuoteRegister To Reply
  5. 05-03-2018, 04:06 PM #5
    DollarBankITAssetMgt
    DollarBankITAssetMgt is offline
    Registered User
    Join Date
    05-03-2018
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    2010
    Posts
    5

    Re: Multi-Line Cells to Rows, retaining info from preceding column.

    [QUOTE=MarvinP;4892450]Hi DollarBank.. and welcome to the forum,

    In a newer Excel tool called "Power Query" for 2010 or "Get & Transform" in 2016 your problem is a snap. You simply need to do a Fill Up process. You can install the Power Query Add-In for free from Microsoft at:


    Then use Fill Up.





    Marvin,

    I don't think this is what I'm looking for. It solves the second part of my problem, but I need a way to split those multi-line cells into rows within the same column first. Note only 4 rows in my first list and 12 rows in my desired list. Any suggestion there?

    Thanks
    Chris
    Reply With QuoteRegister To Reply
  6. 05-03-2018, 03:17 PM #6
    DollarBankITAssetMgt
    DollarBankITAssetMgt is offline
    Registered User
    Join Date
    05-03-2018
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    2010
    Posts
    5

    Re: Multi-Line Cells to Rows, retaining info from preceding column.

    Awesome. Thanks Marvin. I'm going to take that route now.
    Reply With QuoteRegister To Reply
  7. 05-08-2018, 06:29 PM #7
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    19,040

    Re: Multi-Line Cells to Rows, retaining info from preceding column.

    Perhaps this formula based solution will be helpful.
    On sheet 1 columns C:H are populated using:
    Formula: copy to clipboard 
    =TRIM(MID(SUBSTITUTE($B2,CHAR(10),REPT(" ",LEN($B2))),(COLUMN(A:A)-1)*LEN($B2)+1,LEN($B2)))

    I1 displays the number of rows that will be needed using: =SUM(I2:I4)
    I2 and down are populated using: =SUMPRODUCT(--(C2:H2<>""))
    J1 has the value of zero typed in.
    J2 and down are populated using: =SUM(I$2:I2)
    On sheet 2 column A, which may be moved and/or hidden for aesthetic purposes, is populated using: =IFERROR(IF(A1+1<=Sheet1!I$1,A1+1,""),"")
    Column B is populated using:
    Formula: copy to clipboard 
    =IF(A2="","",INDEX(Sheet1!A$2:A$5,IF(ISNUMBER(MATCH(A2,Sheet1!J$2:J$5,0)),MATCH(A2,Sheet1!J$2:J$5,0),MATCH(A2,Sheet1!J$1:J$5,1))))

    Column C is populated using:
    Formula: copy to clipboard 
    =IFERROR(INDEX(Sheet1!C$2:H$3,MATCH(B2,Sheet1!A$2:A$5,0),COUNTIFS(B$2:B2,B2)),"")

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    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. Copy Multi Rows From Multi Sheets Based On Column Value And Create New Workbooks
    By Huskersippi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2017, 04:51 PM
  2. Compress rows into one cell in one column retaining one master record in another column
    By taylorcharley in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-14-2015, 02:22 PM
  3. [SOLVED] Formula to insert text in new column if preceding more than one of the preceding have val
    By aimeecrystalaid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-04-2013, 11:47 PM
  4. [SOLVED] Splitting multi-line cells into rows and moving the values into another column
    By Gav74 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2013, 09:59 AM
  5. Multi-line excel cells into rows
    By walimos in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 08-06-2012, 12:17 PM
  6. Splitting multi-line cells into separate rows
    By jazzper in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-23-2009, 06:19 PM
  7. Insert Line maintaining formatting and formulas from preceding line
    By mangled_us@yahoo.com in forum Excel General
    Replies: 8
    Last Post: 11-30-2005, 09:55 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1