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

[susan12345]

In my excel file I have two worksheets, Sheet 1 and Sheet 2.
In Sheet 1 of column A, data appears thousands of times between two keyword (same or different, I will choose).
I have to copy the data between the keyword in rows of Sheet 2.

[Excel Fox]

Hi,

If you could attach a very small sample with the keywords, or post a picture, that would help understand your requirement clearly

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[santa1234]

Hi,

If you could attach a very small sample with the keywords, or post a picture, that would help understand your requirement clearly

The demo file is attached; please find it.

I have attached the file again because I am not able to download the demo file. Is it a site issue?
Ps. Here is the file too to download in case there is an issue: https://file.io/7OHx0HH2CpxG

[Excel Fox]

Hi - not sure how the output was decided. Could you explain each of the three cases.

[santa1234]

Hi - not sure how the output was decided. Could you explain each of the three cases.


I want to copy and paste the lines between Keywrod1 and Keyword2 from sheet1 to a new cell/row in sheet2 in all cases.
Keywrod1 and Keyword2 appear hundreds of times in the original data and data rows are there between the keywords.

[Excel Fox]

Can you elaborate exactly in the 3 outputs in the next sheet, how was that determined

[santa1234]

Can you elaborate exactly in the 3 outputs in the next sheet, how was that determined

The 3 outputs are given in sheet2 of the attached excel sheet :)

[Excel Fox]

saw the outputs in sheet2 already - but it's not clear how you got those outputs from keyword1 and keyword2. that's why i am asking to explain it.

[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

[santa1234]

Keywrod1 and Keyword2 are themselves keywords. Hope it is clear now.
Instead of keyword1 and keyword2 let's say "Keywrod1" is "" and keyword2 as ""

Search: "Keywrod1" alias ""
Search: "Keywrod2" alias ""
copy text between them in cells as shown in the excel sheet.

[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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16735&viewfull=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” (https://en.wiktionary.org/wiki/walk_through) or “step by step” (https://en.wiktionary.org/wiki/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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16732&viewfull=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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16733&viewfull=1#post16733
( reduced data https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16735&viewfull=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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16734&viewfull=1#post16734 .

[DocAElstein]

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/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=18381&viewfull=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/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=18381&viewfull=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/showthread.php/2824-Tests-Copying-pasting-Cliipboard-issues?p=18394&viewfull=1#post18394

[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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=18395&viewfull=1#post18395











That’s it , for now :)


Alan

[DocAElstein]

spare post for extra solution later