+ Reply to Thread
Results 1 to 7 of 7

Compress rows into one cell in one column retaining one master record in another column

  1. #1
    Registered User
    Join Date
    06-08-2015
    Location
    Glasgow, UK
    MS-Off Ver
    2019
    Posts
    37

    Smile Compress rows into one cell in one column retaining one master record in another column

    I have 3 large csv file (310678 rows).
    Each file contains components of an assembly. At the moment each component is on an individual line with the Assembly name and description on the same line.

    We need to compress the component descriptions (Column G) into one cell, but onto an individual line within the cell so we only have one line per Assembly intead of each Assembly having the number of lines of the components within each Assembly.

    It does not matter what is left in the Component part number cell of the line as this won't be used.

    I have attached two files showing examples of what we have now and what we would like to achieve.

    Hoping for some help! Thank you in advance.

    Charley

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compress rows into one cell in one column retaining one master record in another colum

    VBA code to produce CSV file

    Please Login or Register  to view this content.

    output file is "test.csv"
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-08-2015
    Location
    Glasgow, UK
    MS-Off Ver
    2019
    Posts
    37

    Re: Compress rows into one cell in one column retaining one master record in another colum

    Many thank for your time.

    I'm really sorry but I can't follow the instructions and Sheet 3 on the output file has merged the duplicate records but not the full component descriptions.

    I'm not very expienced with Excel and so apologies for the question.

    Charley

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compress rows into one cell in one column retaining one master record in another colum

    See the attached: click RUN button on sheet1 (Column K).

    The CSV file is produced as a separate file.

    The CSV file produced is also attached.
    Attached Files Attached Files
    Last edited by JohnTopley; 12-14-2015 at 12:26 PM.

  5. #5
    Registered User
    Join Date
    06-08-2015
    Location
    Glasgow, UK
    MS-Off Ver
    2019
    Posts
    37

    Re: Compress rows into one cell in one column retaining one master record in another colum

    I've enabled macros but the Run button isn't producing the separate file.

    Looking at your Test.csv file, is there a way of replacing the | symbols with an actual line break so that the text appears on a separate line within the cell?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Compress rows into one cell in one column retaining one master record in another colum

    The file is produced in the same library that the Excel file resides. I have no problem in creating the file.

    Unfortunately I don't know a way of inserting the line feed that I assume you did manually in your sample CSV file.

    Inserting the "Alt+Enter" character ( CHR(10) )results in data being put in column A (first field).

  7. #7
    Registered User
    Join Date
    06-08-2015
    Location
    Glasgow, UK
    MS-Off Ver
    2019
    Posts
    37

    Re: Compress rows into one cell in one column retaining one master record in another colum

    OK, than you for you time.

+ 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: 9
    Last Post: 05-27-2015, 06:28 AM
  2. [SOLVED] How to copy only non-blanks to the parallel column by retaining the cell positions?
    By papusale in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2015, 03:56 PM
  3. Compress Column
    By phillyfilly in forum Excel General
    Replies: 2
    Last Post: 12-11-2012, 03:32 PM
  4. Copy rows from master sheet to other sheets if certain cell in column is blank
    By nadiaraciti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2011, 02:05 AM
  5. retaining referenced cell when inserting column
    By easty in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-20-2010, 08:32 AM
  6. Transpose multiple text-based columns to single column while retaining record ids
    By SchoobsVT in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-06-2010, 12:26 PM
  7. Compress a column
    By Harrymo in forum Excel General
    Replies: 3
    Last Post: 01-18-2006, 10:10 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