Copy data from multiple rows between two keyword and paste the data in row(s) of single cell in sheet2

**DocAElstein** · 10-06-2022, 02:52 PM

Hello again, santa susan.
Before I give you a couple of solutions to this thread , a few other things…….

Thread Title
You thread title is good. It is a good single summary of what you want.

Sample File
Your file shows well what you want. But the sample size is still a bit big. When asking for help in a forum it helps a lot if you can keep the size down to the absolute minimum required to demonstrate and check all possible scenarios.
I will be using a further reduced size file which I made from your file
VBA row to cell1 reduced data.xls : https://app.box.com/s/qne60lkrfp30d50w444gedzjg6b7nyat
https://excelfox.com/forum/showthrea...ll=1#post16735

Requirement explanation
Your explanations are much too brief, they only vaguely give some idea of what you want. Possibly you have difficulty with English language.
Whilst your explanations are not incorrect, they lack the detail needed for someone who is trying to understand your requirement fir the first time.
Typically a “walk through” or “step by step” type explanation is needed.

But I think I now understand what you want, maybe this is a better explanation.
What is wanted:
This is what I have ( The “Input” or “before” )

Column A of sheet 1 has text in some cells. ( https://excelfox.com/forum/showthrea...ll=1#post16732 )

As you look down the column, you will see that some cells with text in them have a keyword of Keywrod1 and some cells have the keyword of Keyword2

What is wanted as output in sheet 2 ( https://excelfox.com/forum/showthrea...ll=1#post16733
( reduced data https://excelfox.com/forum/showthrea...ll=1#post16735 ) )

The text in all cells between any occurrence of the Keywrod1 and Keyword2 in the text in column A, and the complete text in the cells with that Keywrod1 and Keyword2 occurrence, should be combined into a single cell on sheet2.
So for the sample data we will finally have 3 cells of text, as shown in the sample file

In addition we can see from the sample data, that the empty cells within the occurrence of aKeywrod1 and Keyword2 in cells, will result in an empty line of text in the cell of the combined text in sheet 2. See for example the third case shown here: https://excelfox.com/forum/showthrea...ll=1#post16734 .

**DocAElstein** · 10-06-2022, 02:55 PM

A solution.

Solution 1

Advanced complex

A possible solution to this would combine some of the various string manipulation ides that I have already done for you, and something new for you: the use of the Windows Clipboard in conjunction with an Excel range .Copy

Use of the Windows Clipboard, (Copying and Pasting with it in Excel)
The basic idea of what I am doing here:
As a start point I want to analyse the text in your column A using my function again. But the difference here is that I will not look directly at the text, but instead, first copy the text to the Windows Clipboard, and then look at that.
Use of the Windows Clipboard, Copying and Pasting with it in Excel)
Three things to consider here, .Copy , Windows.Paste, VBA DataObject
Copy
We find that if we .Copy an excel range, then it gets put in several clipboards, including the Windows Clipboard. What the Windows Clipboard holds is usually mostly simple unformatted text and the only characters other than text are usually only the simplest text formatting “invisible” ones of , a Tab, Carriagereturn and Linefeed.
Paste
Similarly, we find that using the worksheet .Paste method will put this copied data, in simple text form into a range. Usually this information comes from that simple text form held in the windows clipboard.
dataobject
In VBA we can both put into and access the simple text data in the windows clipboard using the dataobject
So….
What we can do is,
_ Copy the data from column A
_ Use the dataobject to get at that data from the windows clipboard, modify it in some way and then put the modified form back in the windows clipboard using the dataobject. (We will attempt to modify it in such a way that if we then paste it out to sheet 2, then it will appear in the final form we want
_ Paste the modified data into sheet 2

Copy and analyse the data in the windows clipboard using my function
See here : https://excelfox.com/forum/showthrea...ll=1#post18381
( ** I am using my reduced data version of your test file:
VBA row to cell1 reduced data.xls https://app.box.com/s/qne60lkrfp30d50w444gedzjg6b7nyat )
Conclusions
The row separator in the windows clipboard is that most typically used for a new line in computing, a Carriage return and a Line feed ( in VBA coding vbCr & vbLf ).
For a new line within a cell, we have the typical convention in Excel of just the Line feed ( in VBA coding, vbLf )
In the case of 2 or more lines within a cell, the entire string for the cell is enclosed in a pair of quotes. ( I expect this is to help avoid the vbLf being taken as a new row )

The implications of the conclusions are that to consolidate / reduce cells into a single cell, we will need to do 2 things:
_ replace the vbCr & vbLf for empty cells with a vbLf. This will have the effect of giving an empty line within the cell text in place of an empty row.
_ remove all quotes, but then add just one pair of quotes enclosing all the text and empty lines that should appear in one cell.
( Of course we will need to do some coding to locate the keywords so as to determine which cell text and any empty cells that are to be in one cell in the output. This may actually be quite a complex coding.)

Detailed explanation of solution
This is an advanced solution, and I have tried to give good detailed explanations of most code lines in the 'comments.
Here is an attempt to summarise
Rem 1 copies the used range which has the effect of putting the entire data range in the clipboards, including the windows clipboard, which we are interested in.
Rem 2This gets the text that is held in the windows clipboard into a string variable, StringBack
Rem 3 is a typical code type section before some looping which then repeats in some looping process. We get positional information here about the fist keyword
( There is also a check that we have a matching next second keyword, so as not to loop further in the case of a first keyword towards the end of the data, but no final second keyword )
Rem 4 This is the main section, quite complex and I can only summarise here
'4a) We are at the start of a main outer loop, which will loop as long as we have a next pair of keywords. This is a second typical code section before a second inner loop which is looping for all cells within any pair of keywords. In this section we get the text from the first cell
'4b) We are stepping through all the cells within a keyword pair
'4c) At this point we have the complete text for a new single cell and are just before the end of the main loop, so almost ready to go on to the next pair of keywords. We need to format this text to allow us to have a form to put back in the windows clipboard that will
.Paste out in the consolidated cell format we want for the final output. Two main things are done:
_ we may need to add an enclosing quote that tells the clipboard and Excel that there are multi line text to paste into a single cell.
_ We need to add a final trailing vbCr & vbLf as this will be recognised as meaning a new row, ( next output cell in our case )
( These two things we determined here: https://excelfox.com/forum/showthrea...ll=1#post18381 )
'4c(ii) This is a typical end section at the end of a loop, which sets the info necessary for the next loop, and gets similar info to Rem 3

Rem 5 Put our final manipulated text in clipboard
Rem 6 .Paste out from windows clipboard

Here is Solution 1
Sub ConsolidateLines_Solution1()
https://excelfox.com/forum/showthrea...ll=1#post18394

**DocAElstein** · 10-06-2022, 02:55 PM

Second Solution

Solution 2

The first solution, Solution 1, is somewhat complex but represents a fairly efficient professional solution. It follows the typical form of more advanced and more efficient solutions, whereby the entire data is taken into VBA in some way, ( Solution 1 did it via the windows clipboard to get a single string of all the data ) , all the main work and manipulation is done within VBA, and then the final output data is put in the spreadsheet in one go.
But here in Solution 2, I do a more basic “Spreadsheet interaction” type coding solution. Such solutions are often much easier for the novice to understand, but often the extra interactions with the spreadsheet make things inefficient. We often say that every interaction of coding with a spreadsheet is like “slamming the brakes on”

The coding has a similar structure to that for the Solution 1, but is a bit simpler and easier to follow. I have numbered the sections such that they correspond to similar sections in the two solutions.

Rem 3 gets us the first cell we are interested in for a keyword pair, just before we go into the main looping. You can see that at the end of the main loop we have the typical section again, '4c(ii) , getting us similar information for the next first cell in a keyword pair.
'4b) is the section to make a new cell for output, nut in this solution that information is pasted out each time, ( so we have no final sections in the coding to paste oput all in one go, as we did in Solution 1 )

Here is Solution 2
Sub ConsolidateLines_Solution2()
https https://excelfox.com/forum/showthrea...ll=1#post18395

That’s it , for now

Alan