Excel Spreadsheet split numbers into cells and add value below the number if it was in the list-CodePudding

I have an Excel spreadsheet similar to the below with 2K-3K lines. There is a list of results that I want to first split (based on - from column B) then sort them in a way based on the numbers the Test* is in. So in the end I want the names of everyone in column A that selected 1, then the ones selected 2, etc. For example:

      A       |    B       |               
Row1 Test1    |  1-2-3-4-5 |
Row2 Test2    |  1-3-4-6-7 |
Row3 Test3    |  2-3-4-6   |

Create a new sheet and result should be

      A     |    B      |     C     |    D      |     E     |     F     |     G     |       
Row1  1     |    2      |     3     |    4      |     5     |     6     |     7     |
Row2 Test1  |   Test1   |   Test1   |   Test1   |   Test1   |   Test2   |   Test2   |
Row3 Test2  |   Test3   |   Test2   |   Test2   |           |   Test3   |           |
Row4        |           |   Test3   |   Test3   |           |           |           |

Column F has Test2 first then Test3 since these two contain value 6 in their list.

Thank you for the help.

CodePudding user response：

You can use this formula in A7:

=LET(TestNames,$A$1:$A$3,TestResults,$B$1:$B$3,
FILTER(TestNames,ISNUMBER(FIND(A$6,TestResults)),""))

Based on this sheet