Transpose data based on field separator

[kv.singh]

Hi!
can anyone on this forum provide for following description :-

Line below each record is "------------------------------------" is record separator. code should search this record separator
and wherever this record separator is found copy rows above and paste it in sheet 2 side by side(transpose)
i.e. PASTE ALL DATA OF ROW 1 TO A1 ON SHEET2. and then again repeat this process for second record and so on.
Above explanation will help in providing solution.

[Dante Amor]

The end goal is sheet4?

On your sheet you have "Records" of 5 lines and also 4 lines.
Do you only want the 5 lines?

Attachment 691017

Below the double line you have rows to copy. Is that correct?

Attachment 691021

Another pattern I see is that the "Records" start with a number, is that correct?

[kv.singh]

Yes , The end goal is sheet4.
Yes , I want all records(no matter how much line a record has). If record contains 4 lines than fifth row should be blank
in Sheet 4
Yes, Above the double line I have rows to copy.
Yes, First row of each record start with number.

[kv.singh]

[JeteMc]

This is a formula based proposal that employs a lot of helper columns populated by relatively simple formulas.
The first helper column (D) is populated using: =IF(VALUE(LEFT(TRIM(A2),4))< 36000,1,IF(D1<=4,SUM(D1,1),""))
The next four helper columns are populated using: =IF(D1=COLUMNS($A1:A1),SUM(D1,1),"")
The next helper column is populated using: =IF(LEFT(A2,1)="-","",AGGREGATE(15,6,D2:H2/ISNUMBER(D2:H2),1))
The next five helper columns are populated using: =IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$1))/($I$2:$I$100=COLUMNS($A$1:A$1)),ROWS($A$1:$A1))),"")
The final result is in column O which is populated using: =CONCATENATE(J2,K2,L2,M2,N2)
Let us know if you have any questions.

[kv.singh]

Thanks. Your formulas are excellent. But giving result for only first 18 records.

My original file contains 1000 records(each record is seperated by "--------------".
Kindly update formulas so that I can get result for all 1000 records.

[JeteMc]

The only formula that should need modification is the formula for columns J:N.
As for the other formulas they can dragged down to the last row of the last record.
I am guessing the 1,000 records may occupy 100,000 rows so the formula in cell J2 could be modified to read:

Please Login or Register  to view this content.

Once the formula is pasted into cell J2 then drag the fill handle over to cell N2 and then, while cells J2:N2 are still selected, drag the fill handle down to the last row of the last record.
Note that the formula will possibly take some time to complete calculation, so please be patient.
Let us know if you have any questions.

[kv.singh]

Excellent solution. My problem is solved. Thanks once again for your time and efforts..

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.