Sorting data

[Chehak]

Hi All,

Hoping I could get some help with my question below -

I have data in the format below -
Apple
3
4
10
Ball
1
78
9
Cat
345
67
08
Dog
34
11
2437


I need to transpose the numbers in the rows next to the names. Now I know I could do them one by one but I have thousands of numbers for names and it is def gonna be time consuming.

Is there a way to do transpose the numbers next to the associated names all at once?

I am open to suggestions or a different way to get this done faster. Thanks for the help in advance.

[#2]

@Chehak 

Hello, maybe there is something more simple with an excel formula. Attached is an excel file with a possible solution using power query.

Just copy your data in the table "Information" and go to the ribbon data > refresh all (like a pivot table)

[#3]

@Chehak Attached file contains two formula based solutions. One that will work in MS365 / Excel 2021. The other with traditional formulae that need copy and paste down and across. See which one works for you.

As an alternative, something similar can be achieved with Power Query, though less straightforward if you are not accustomed with the tool.

Edit: New file attached including the PQ solution.

[#4]

@Riny_van_Eekelen

Thank you so much. The second formula is pure magic! Some of the names have more than 3 numbers assigned to it. It is not limited to 3 numbers only. Its my bad, i forgot to mention that before. I tried to apply the second one to the name with more than 3 numbers, but it didnt work. As far as i understand, you have designed this to transpose 3 cells after every name into columns. Right? Is there anything that you can suggest to use to get it all sorted?

The data may look more like

Apple
1
2
3
Ball
4
5
6
Cat
7
8
Dog
9
Egg
10
11
12
13
14

I am gonna google about power query to learn more.
Thank you so much for your help!!!

[#5]

@alannavarro

Thank you so much. I will try this one and get back to you.