Rolling cube example

[berti]

I have 16 players that roll number cube (1,2 ...6) once.
In Column A I have player's names and in column B score.
What I would like to do is to list all players that got 1 in column E,
all players that got 3 in column F ... and all that got 6 in column J.
Like in attached example, where John and James got 1 and they are both listed below 1 in column E.


I wonder if that is possible without macros.

In general I would like to "filter" data in particular column of selected table
and then list results of each such value of that particular column in the same order as it is in the original table.

[oldchippy]

You could try this in E2 and drag across to J2 and down to E17:J17

=IF($B2=E$1,$A2,"") then Copy and Paste Special > Values and filter the range

[berti]

Thanks, but this is not exactly what I wanted.

What about without any user action?
So that this would be done automatically?

When I enter John and 1, the John would be listed above column 1, later when I add James and 1, James would appear below John in E3

[oldchippy]

Sorry for the delay in replying, must admit I needed to do a little research into this one.

Hope this is what you want, you can hide columns D:L if you wish, but don't delete them, they are needed in the formulas.

You will note that in M1 the formula is array entered - Ctrl+Shift+Enter - to get the { } around the formula. Then just drag across and down to auto-fill the table.