transposing accourding to values in a cell?

hello all
I am using excel in Office 365
I have columns A to D in my excel sheet.
Column A has a value and some blank cells on various lines: I want to transpose the values in column C and D on the first line until column A changes.
in the attachment, line 3 has the transposed values of column C and D, until the Value in column A changes.
and when it does, I need the same for that record: meaning, column C and D until the next value of A changes, must be transposedAttachment 810999Attachment 811006

No. Where are all the other rows of raw data? Please do not expect us to recreate what you already have right in front of you...

The picture showed it perfectly... but we offer help for fun, for free. We expect YOU to prepare sample sheets, if required. You should not expect US to do so, for you. You already have it, in front of you.

Is this close?

=LET(A,$A$2:$A$28,B,,C,$C$2:$D$28,D,SCAN("",A,LAMBDA(x,y,IF(y="",x,y))),IF((A2<>"")*(A3="")=1,TRANSPOSE(TOCOL(FILTER(C,D=A2),,1)),""))

This might be better:

=LET(A,$A$2:$A$28,B,,C,$C$2:$C$28,D,$D$2:$D$28,E,SCAN("",A,LAMBDA(x,y,IF(y="",x,y))),F,IF((A2<>"")*(A3="")=1,DROP(TRANSPOSE(TOCOL(FILTER(C,E=A2),,1)),,1),""),G,IF((A2<>"")*(A3="")=1,DROP(TRANSPOSE(TOCOL(FILTER(D,E=A2),,1)),,1),""),HSTACK(F,"",G))

Originally Posted by alisam

if I understand correctly the formula, in place of all the 28 I must enter the final line number? meaning, I have a total of 6786 lines.....

Yes. Correct.

Everything is possible... The easiest way is to split it in two...

Acceptable?

Or, a more future-proof version:

=LET(A,$A$2:$D$10000,B,FILTER(A,INDEX(A,,2)<>""),C,SCAN("",INDEX(B,,1),LAMBDA(x,y,IF(y="",x,y))),D,(A2<>"")*(A3=""),IF(D=1,TRANSPOSE(DROP(FILTER(INDEX(B,,3),C=A2),1)),""))

only one number to change, if needed. See file.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.