How to sort and switch the data in the first column and the rest? (With an example)

I don't know how to describe this problem accurately so here is an example. The original sheet looks like this:

a 1 6 7 8 10

b 3 4 9 10

c 1 2 3 4 5

d 1 4 5 8 11

e 2 3 6

f 5 6 7 10 11

g 4 5 7 8 10

Now I want to rearrange this table and put the numbers in the first column, which indicates which numbers "own" which letters, like this:

1 a c d

2 c e

3 b e

4 b c d g

5 c d f g

6 a e f

7 a f g

8 a d g

9 b

10 a b f g

11 d f

I did this manually but does anybody know if there is an easier way to do this? BTW in the real case, all the letters and numbers are names of people or things.

Thank you!

You can attached a sample of your worksheet to have clear idea of your problem.

It seems that your sample above are different from your result.

regards,
Rev12

Thanks Rev12. I tried to upload a screenshot but failed. I copied and pasted the text instead. It's the same. I did the work manually so there might be some typo.

To put it in a simpler way, just how to change a sheet from:

a 1 2
b 2 3 6
c 3 4 5

into

1 a
2 a b
3 b c
4 c
5 c
6 b


Someone suggests pivot table. I'll check out some tutorial later but I'd appreciate it if someone could provide a detailed solution.

Thanks a lot MarvinP! I gotta figure out how to use pivot table to better understand this.

A completely formula based solution without helper column

I am considering example from 1st post and assuming that your example from post #1 ranges from A1 and goes till F7

below formula is to extract the numbers in one column from data copy paste below formula in H1 then hold Ctrl+Shft and then Hit enter to make it array

Formula:

Please Login or Register  to view this content.

drag down

now in I1 use below formula and then drag down and drag right to get result, copy paste below in I1 and the hold CTRL+SHIFT and then hit enter

Formula:

Please Login or Register  to view this content.

change the ranges to suit your need

if this helps then add reputation by clicking little star button at the bottom of this post

Thank you guys so much!

Thanks for the reply and rep truthguy!