I have a table (1) in Excel, with two columns, in which at the first column (A) there are some numbers and at the second column (B) there are some letters. I want to have a method to make another table (2) from (1) to put different letters at the first column then to put in each row the numbers that were corresponded to letters in table (1).

For example, let the table (1) is:

| A | B |
|---|---|
| 1 | a |
| 1 | b |
| 2 | a |
| 2 | c |
| 3 | b |
| 4 | b |

What is a method in Excel which make the following combination table:

| a | 1 | 2 |   |
| b | 1 | 3 | 4 |
| c | 2 |   |   |

in which letters are in first column and in each row there are the numbers that were in relationship with the row's letter in table (1)?

CodePudding user response：

As per below screenshot use below formula to C1 cell.

=UNIQUE(B1:B6)

And following formula to D2 cell then drag down

=TRANSPOSE(UNIQUE(FILTER($A$1:$A$6,$B$1:$B$6=C1)))