+ Reply to Thread
Results 1 to 4 of 4

If a2 does not begin with "| | |", append contents of a2 to cell a1

Hybrid View

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    7

    If a2 does not begin with "| | |", append contents of a2 to cell a1

    Greetings, I am working with a very large spreadsheet (600,000+ rows), and I really only have 2 problems We pulled the data from one system and need to upload into another. I am going to show some example rows with some data omitted. All data exists in Column A (I'll parse it up later):

    Problem1 -
    1 | |700 |061A7350807EC04AE1000000AC167310|20121009014239 |EXTENDED MATERIAL DESCRIPTION |14M |E_GTS_EXPMST|Part info for Justification10/9/2012 | | |
    2 | |700 |0382DC4EBCDE8D38E1000000AC16728A|20120111143750 |WASHER,PROTECTION GP |D6K |SATCHGL |ECCN assigned per P.L. instructions to G.S. 22 Dec 11.
    3 Classification Rationale is on file at Co.X, and is available upon request. Please contact the X Inc. Global Export Compl|iance Program Manage | |

    As I said, all data exists in Column A. Row 1 is fine; row 2 is fine; but row 3 is actually a continuation of row 2, so I want to move its contents from Cell A3 and append it to the end of Cell A2 then delete row 3 - which doesn't seem to be too bad except for problem 2 below.

    Problem 2, further down the same spreadsheet -
    51 | |700 |0960944EEE5BB067E1000000AC16728A|20120323160535 |PANEL-CONTROL |C7.1 |OMANAM |This is a control panel used in an industrial engine.
    52 Based on t|he information found it classifies as a programmable controler as the ones included in heading 8537.
    53 This part was requested in a classification referral. Code approved by L.G. We are still waiting for deisgn control's response to confirm cod|e. |

    In this case, BOTH 52 and 53 are continuing from 51, so I would want to append the contents of A53 to A52, then the new contents of A52 to A51 and delete rows 52 and 53.

    I am not very good with macros, but I think I need one in this case. I would be happy even with multiple macros and/or formulas, and the pipes in my data don't bother me at all as they are easily removable.

    Any help from anybody would earn my undying gratitude!
    Doug

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: If a2 does not begin with "| | |", append contents of a2 to cell a1

    This ...

    Sub x()
        Dim iRd As Long
        Dim iWr As Long
        Dim iEnd As Long
    
        
        iWr = 1
        
    
        With Range("A:A")
            iEnd = .Cells(Rows.Count).End(xlUp).Row
            
    
            Do
                For iRd = iWr + 1 To iEnd
                    If IsEmpty(.Cells(iRd).Value2) Then
                        Exit For
                    ElseIf Left(.Cells(iRd).Value2, 3) = "| |" Then
                        Exit For
                    Else
                        .Cells(iWr).Value = .Cells(iWr).Value2 & .Cells(iRd).Value2
                        .Cells(iRd).ClearContents
                    End If
                Next iRd
                iWr = iRd
            Loop While iWr < iEnd
        End With
    End Sub
    ... converts this

          ------A------
      1   | |700 Test 1
      2   | |700 Test2 
      3   Test 3       
      4                
      5   | |700 Test 4
      6   Test 5       
      7   Test 6       
      8   Test 7
    ... to this:

          ---------------A---------------
      1   | |700 Test 1                  
      2   | |700 Test2Test 3             
      3                                  
      4                                  
      5   | |700 Test 4Test 5Test 6Test 7
    Last edited by shg; 05-30-2013 at 06:07 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-30-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: If a2 does not begin with "| | |", append contents of a2 to cell a1

    That is truly a thing of beauty. Thanks so much!


  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: If a2 does not begin with "| | |", append contents of a2 to cell a1

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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