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