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

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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!