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

**susan12345** · 09-10-2022, 03:59 AM

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** · 09-10-2022, 09:46 AM

**santa1234** · 09-10-2022, 12:56 PM

Originally Posted by 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

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** · 09-11-2022, 05:59 PM

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

**santa1234** · 09-11-2022, 10:42 PM

Originally Posted by Excel Fox

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** · 09-11-2022, 11:29 PM

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

**santa1234** · 09-12-2022, 05:10 AM

Originally Posted by Excel Fox

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** · 09-12-2022, 02:18 PM

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.

**santa1234** · 09-27-2022, 05:18 PM

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

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

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