+ Reply to Thread
Results 1 to 9 of 9

Creating a clone line of data to remove the <alt> + Enter.

  1. #1
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    568

    Creating a clone line of data to remove the <alt> + Enter.

    I have inherited a project from a previous employee and for the life of me I have no idea of why it did this the way he did.

    Attached is an example of what I am dealing with, unfortunately is a HUGE file so doing this manually is just not an option.

    Sheet 1 has two highlights yellow and green, the corrected output is on Sheet2. Essentially what they did was in Col B is have the part numbers for the product listed using “alt” + enter. This does not work for what I need to do (inventory control) so I need some code that will spit out Row 2 into 4 rows with the same data for each row as depicted on sheet2.

    FYI the actual list is over 1000 lines long and the columns run all the way out to DA….. so you see how huge of a manual project this would be…..

    Thank you for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,984

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,608

    Re: Creating a clone line of data to remove the <alt> + Enter.

    How about
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,289

    Re: Creating a clone line of data to remove the <alt> + Enter.

    If interested, a formula-based approach:

    A2, copied down
    =IFERROR(INDEX(Sheet1!$A$2:$A$9,MATCH(0,INDEX(--(COUNTIF($A$1:A1,Sheet1!$A$2:$A$9)=1+LEN(Sheet1!$B$2:$B$9)-LEN(SUBSTITUTE(Sheet1!$B$2:$B$9,CHAR(10),""))),0),0)),"")

    B2, copied down
    =IF(A2="","",TRIM(MID(SUBSTITUTE(CHAR(10)&INDEX(Sheet1!$B$2:$B$9,MATCH(A2,Sheet1!$A$2:$A$9,0)),CHAR(10),REPT(" ",125)),125*COUNTIF($A$2:A2,A2),125)))

    C2, copied across and down:
    =IF($A2="","",INDEX(Sheet1!C$2:C$9,MATCH($A2,Sheet1!$A$2:$A$9,0)))
    Attached Files Attached Files
    Glenn



  5. #5
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    568

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Thank you ALL for your wonderful suggestions.

    jindon your replay seems to work the best for me, but the code is really over my head.

    What happens if the number of colulms goes from A to DF. Is there something that needs to be done to the code to accommodate that larger span?
    Last edited by sungen99; 10-12-2019 at 10:46 AM. Reason: adding more info

  6. #6
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,608

    Re: Creating a clone line of data to remove the <alt> + Enter.

    You're welcome & thanks for the feedback

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,984

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Just try.

    It should work as long as the data range is not separated by the blank column/row and the 2nd column has Line Feed character(s).

  8. #8
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    568

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Jindon, lets say the range i needed to change was NOT Col B, but say Col D? how could i alter the code to accommodate that?

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,984

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Change to
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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