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

Try,

In G2, array formula (Ctrl+Shift+Enter) copied down :

=TEXTJOIN(", ",,IF(ISNUMBER(SEARCH(F2&CHAR(10),B$2:B$10&CHAR(10))),A$2:A$10,""))

bosco_yip

Wasn't the TEXTJOIN a new function with office 365 and excel 2019, so there will be no CSE. If the poster has a previous version he will not have TEXTJOIN. I stand corrected if need be.

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.

So the answer is, if they do not have office 365 or excel 2019, they can not use the TEXTJOIN function.

Quote from [email protected]

So the answer is, if they do not have office 365 or excel 2019, they can not use the TEXTJOIN function.

2 options,

1] You can use a Textjoin UDF ( using Google to search)

2] Used a longer formula or helper columns to obtain the target.

Regards

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.