Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Concatenate 4 rows, keeping carriage returns, and cleaning up

  1. #1
    Board Regular
    Join Date
    Nov 2005
    Location
    Tokyo
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Concatenate 4 rows, keeping carriage returns, and cleaning up

    Hiya,

    (It's actually concatenating twice, not just 4 rows... sorry, i couldn't go back and edit the title!)

    i have this data in Column A is 8 rows of data that does not change structure:


    ~~

    T1A02 (C) [97.1]
    Which agency regulates and enforces the rules for the Amateur Radio Service in the United States?
    A. FEMA
    B. The ITU
    C. The FCC
    D. Homeland Security
    ~~


    It goes on for 3500 lines, and what needs doing in Column B is:

    * of course, ignoring the "Start" and "End", as they are not part of the data........
    * Put a 1 on the first line (a blank in the data), which is the 1st line after the double tildes
    * Join (concat the 2nd line starting with T****, add a "@@", and then the 3rd line which is a wrapped sentence
    * Concat lines 4 thru 7 (items A thru D), keeping the carriage returns
    * Put a "End" where the "~~" is. (i'm not sure if this needs to stay to enable further processing, but i don't need the "~~"
    * Go back and delete the original data in rows 4,5,6,7 or put a DD in column B for those values in column A (that way i can go back and delete them myself using a filter.

    Note that all of the above happens column B, where the data is in column A

    i'm not the greatest at VB, but can read a little bit and troubleshoot a bit, but the above is too much for my noggin.

    If an Excel god could push me in the right direction by showing me some VBA or a formula, either one, i'd be grateful and downright happy!

    For what it's worth, i will use the data by importing it into a flash card program for studying purposes.

    Any miracle workers out there?

    Thank you from Tokyo!

    Noppojp
    Last edited by noppojp; Jun 13th, 2015 at 04:05 AM. Reason: botched the title, needed to fix it for clarity

  2. #2
    Board Regular
    Join Date
    Nov 2005
    Location
    Tokyo
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate 4 rows, keeping carriage returns, and cleaning up

    Quote Originally Posted by noppojp View Post
    Hiya,

    (It's actually concatenating twice, not just 4 rows... sorry, i couldn't go back and edit the title!)

    i have this data in Column A is 8 rows of data that does not change structure:


    ~~

    T1A02 (C) [97.1]
    Which agency regulates and enforces the rules for the Amateur Radio Service in the United States?
    A. FEMA
    B. The ITU
    C. The FCC
    D. Homeland Security
    ~~


    It goes on for 3500 lines, and what needs doing in Column B is:

    * of course, ignoring the "Start" and "End", as they are not part of the data........
    * Put a 1 on the first line (a blank in the data), which is the 1st line after the double tildes
    * Join (concat the 2nd line starting with T****, add a "@@", and then the 3rd line which is a wrapped sentence
    * Concat lines 4 thru 7 (items A thru D), keeping the carriage returns
    * Put a "End" where the "~~" is. (i'm not sure if this needs to stay to enable further processing, but i don't need the "~~"
    * Go back and delete the original data in rows 4,5,6,7 or put a DD in column B for those values in column A (that way i can go back and delete them myself using a filter.

    Note that all of the above happens column B, where the data is in column A

    i'm not the greatest at VB, but can read a little bit and troubleshoot a bit, but the above is too much for my noggin.

    If an Excel god could push me in the right direction by showing me some VBA or a formula, either one, i'd be grateful and downright happy!

    For what it's worth, i will use the data by importing it into a flash card program for studying purposes.

    Any miracle workers out there?

    Thank you from Tokyo!

    Noppojp


    i have a correction to make.
    The 2nd concatenation should go in Column C, next to the 1st concatenated values that were put in Column B.
    So, Column B would have the Code and the Question, and Column C would have the concatenated answers A through D.

    Thank you so much.

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Concatenate 4 rows, keeping carriage returns, and cleaning up

    I am about to go to sleep, so I won't be able to follow up on this with you for a few hours, but the best I can tell, this macro seems to do what you asked for...

    Code:
    Sub NumberBlanksConcatenateTwice()
      Dim Index As Long, LastRow As Long, Blanks As Range, Cell As Range
      On Error GoTo NoBlanks
      LastRow = Cells(Rows.Count, "A").End(xlUp).Row
      Set Blanks = Range("A1:A" & LastRow).SpecialCells(xlBlanks)
      For Each Cell In Blanks
        Index = Index + 1
        Cell.Value = Index
        Cell.Offset(, 1) = Cell.Offset(1).Value & "@@" & Cell.Offset(2).Value
        Cell.Offset(, 2).WrapText = True
        Cell.Offset(, 2) = Join(Application.Transpose(Cell.Offset(3).Resize(4).Value), vbLf)
        Cell.Offset(, 2).EntireRow.AutoFit
        Cell.Offset(3).Resize(4).EntireRow.Delete
      Next
      Columns("A").Replace "~~~~", "End", xlWhole
    NoBlanks:
    End Sub
    Note: Make sure to widen Column C enough so that the longest single line will fit without wrapping.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular
    Join Date
    Nov 2005
    Location
    Tokyo
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate 4 rows, keeping carriage returns, and cleaning up

    Wow, that was great! It got me 95% the way there. Thanks so much. There were some minor hickups in the data, lines that i did not notice, but you nailed a very large majority of the data with your code. i'm in study mode now. Thanks a ton!

    Noppo

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Concatenate 4 rows, keeping carriage returns, and cleaning up

    Quote Originally Posted by noppojp View Post
    Wow, that was great! It got me 95% the way there. Thanks so much. There were some minor hickups in the data, lines that i did not notice, but you nailed a very large majority of the data with your code. i'm in study mode now. Thanks a ton!
    You are quite welcome, I am glad I was able to help.

    Just out of curiosity, what were the "hiccups in the data", perhaps I can code around them for you?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Nov 2005
    Location
    Tokyo
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate 4 rows, keeping carriage returns, and cleaning up

    Quote Originally Posted by Rick Rothstein View Post
    You are quite welcome, I am glad I was able to help.

    Just out of curiosity, what were the "hiccups in the data", perhaps I can code around them for you?
    Hey Rick! The hiccups in the data were as follows (below). They were perfect as data goes, but i simply did not notice them as i went through 3500+ lines myself. See the bold. You will see that the question numbering changes. It's simply an explanation of what's to follow below it, e.g., what the questions below that statement are about. The question numbering changes should have been obvious to me, but they weren't at first pass.


    T1A14 (A) [97.303(d)]
    What must you do if ..........?
    A. Stop .....
    B. Nothing, because this ....
    C. Establish contact with ....
    D. Change to .....
    ~~

    T1B - Authorized frequencies: frequency allocations; ITU regions; emission modes; restricted sub-bands; spectrum sharing; transmissions near band edges

    T1B01 (B)
    What is the ITU?
    A. An agency of the ...
    B. A United Nations ...
    C. An independent ....
    D. A department of ....
    ~~


    i'm up and running now, but i can use the fix on a different bank of questions. This bank is the 1st of 2.

    Kind Regards,
    Noppo

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Concatenate 4 rows, keeping carriage returns, and cleaning up

    What should happen with that line... can it be deleted (by my code) or does it need to remain? If it has to remain, which blank gets the number the one above it or below it?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Nov 2005
    Location
    Tokyo
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate 4 rows, keeping carriage returns, and cleaning up

    Quote Originally Posted by Rick Rothstein View Post
    What should happen with that line... can it be deleted (by my code) or does it need to remain? If it has to remain, which blank gets the number the one above it or below it?
    Thanks Rick! That line can be in the first concatenate. So, it can be added below the question, but with a space between it and the question. It seems like such a waste to ignore it, especially since being below the question would not bother anything.

    My bad Rick. i should have stated what needs to be done with it.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •