Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

  • I have a large spreadsheet listing requirements, one of the accompanying columns lists multiple tests associated with each requirement. I need to re-organize the table to be a list of tests with all the associated requirements in next column.

    I've been trying to do this using formulas due to limited macro experience but any help/advice is welcome. Intermediary steps is ok too, just can't do this manual as my spreadsheet is over 3k requirements with 1k different tests. Uploaded sample xlsx, please help!


  • Using Textjoin function for the above formula :

    1] Excel 2019, required CSE entry.

    2] Office 365 with dynamic functions subscribed, CSE entry doesn't required

    3] Office 365 without dynamic functions subscribed, required CSE entry.

  • 1. Your data is not presented consistently in your spreadsheet. Sometimes you have Test-1 and other times you have Test -1. Spacing is importing in the formattting of your data to get proper results.

    2. An alternative solution is using Power Query to split your data and then unpivot it.

    3. Once unpivoted, it is loaded into the Data Model (Power Pivot)

    4. In Power Pivot, create a new measure using concatenex (DAX feature)

    You can see it all in the attached file.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!