Concatenate 4 rows, keeping carriage returns, and cleaning up

[noppojp]

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:

<Start>
~~

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
~~
<End>

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

 

[noppojp]

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:

<Start>
~~

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
~~
<End>

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.

 

[Rick Rothstein]

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...

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.

 

[noppojp]

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

 

[Rick Rothstein]

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?

 

[noppojp]

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.

<START>
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 ....
~~
<END>

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

 

[Rick Rothstein]

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?

 

[noppojp]

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.