Transposing multiple rows in columns for each unique name in column A

[acechapo95]

I would like to transpose multiple rows into columns as per the spreadsheet i've attached.

The left hand side is much much larger and takes far too long doing this manually.

The right hand side is what I want to convert the left hand side too.

Is there an easier way of doing this with formulas?

Thanks!

[Glenn Kennedy]

Q2:
=IF(R2="","",INDEX($E$1:$K$1,1+MOD((ROWS(Q$2:Q2)-1),7)))

R2:
=IFERROR(INDEX($A$2:$A$7,1+INT((ROWS(R$2:R2)-1)/7)),"")

S2:
=IFERROR(INDEX($E$2:$K$7,1+INT((ROWS(S$2:S2)-1)/7),1+MOD((ROWS(S$2:S2)-1),7)),"")

all copied down.

[acechapo95]

you sir are a genius and a life saver. Thank you!

[JohnTopley]

@Glenn: The above formulae appeared as CSE entries (in 2010) even though you did not state this as required in your post: similar to the SUMPRODUCT case ?

[Glenn Kennedy]

Yes, JT. Those formulae should work fine in 2010 without CSE. This is really odd.

[Glenn Kennedy]

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 select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[Czeslaw]

PQ

Please Login or Register  to view this content.

[didierk]

I have a similar problem as the first question, but with uneven number of columns to start with.

Could I still use the same principle and set the selection to the largest number of columns?

[JohnTopley]

You should open a new thread but ..

Helper column in H & i

in A16

=INDEX(A$4:A$11,MATCH(ROWS($1:1),$I$4:$I$11,1))

copy across to B and down

in C16

=INDEX($C$4:$F$11,MATCH(ROWS($1:1),$I$4:$I$11,1),COUNTIF($E$16:$E16,$E16))

copy down

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread