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

[DocAElstein]

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

[DocAElstein]

spare post for extra solution later

[DocAElstein]

@ susan santa 12345 et al

You continue to post short badly explained questions, and either ignore or don’t understand the various things I have said to you.

My best guess is that you are either
_ a Bot,
_ a total idiot,
_ just trying your luck at getting Homework questions answered quickly
_ just trying your luck at answering someone else’s questions and have no idea or interest in them yourself
_ deliberately trying to be a pain in the arse.

I will probably delete, close , ban you, or some combination in a few days to tidy the place up a bit, unless you improve somehow

Alan

[DocAElstein]

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 .

---

---