Hi There,
Hoping someone may have a suggestion for me (VBA, or formula, or other). I want to transpose certain cells based on a condition in a separate column (but same row). To illustrate with an example, I would like all values from column C to be transposed into the relevant row depending on Column B's value. E.g. Column B to contain one line of "Jonathon" and all items in Column C against Jonathon's name are transposed in Row 1. Then Same for other names in Column B. See example of initial data below (A-C columns), and then desired format (E-L columns).
I've seen the INDEX formula used to transpose a certain number of values, but in my case the number of values to be transposed will range from 3 to about 8, so I can set a certain amount in the formula.
Would appreciate any ideas. Thank you in advance!
A B C D E F G H I J K L
<colgroup><col><col><col span="10"></colgroup><tbody>
</tbody>
Hoping someone may have a suggestion for me (VBA, or formula, or other). I want to transpose certain cells based on a condition in a separate column (but same row). To illustrate with an example, I would like all values from column C to be transposed into the relevant row depending on Column B's value. E.g. Column B to contain one line of "Jonathon" and all items in Column C against Jonathon's name are transposed in Row 1. Then Same for other names in Column B. See example of initial data below (A-C columns), and then desired format (E-L columns).
I've seen the INDEX formula used to transpose a certain number of values, but in my case the number of values to be transposed will range from 3 to about 8, so I can set a certain amount in the formula.
Would appreciate any ideas. Thank you in advance!
A B C D E F G H I J K L
ABC | Jonathon | Cat | Jonathon | Cat | Dog | Bird | Fish | Elephant | Panda | Bear | |
XYZ | Jonathon | Dog | Peter | Dog | Bird | Fish | |||||
DUA | Jonathon | Bird | Jessica | Elephant | Panda | Dog | Bird | ||||
WQH | Jonathon | Fish | |||||||||
FDI | Jonathon | Elephant | |||||||||
PDU | Jonathon | Panda | |||||||||
IDJ | Jonathon | Bear | |||||||||
JUR | Peter | Dog | |||||||||
FOR | Peter | Bird | |||||||||
DEG | Peter | Fish | |||||||||
IMB | Jessica | Elephant | |||||||||
PED | Jessica | Panda | |||||||||
IRF | Jessica | Dog | |||||||||
IND | Jessica | Bird |
<colgroup><col><col><col span="10"></colgroup><tbody>
</tbody>