Forum Discussion

Khalifa_007's avatar
Khalifa_007
Copper Contributor
Feb 13, 2024

how to find intersection value in excel which have multiple line in one cell

I aim to determine the common parameter value when selecting two or more rows from the first column. Accordingly, the shared parameter should be displayed in the third row based on the selected rows.

this is the excel table

 

 

 

This is the desired output 

excel
Formulas and Functions

1 Reply

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    Feb 15, 2024

    Khalifa_007 If the TEXTJOIN and FILTER functions are available in your version of Excel, you might be able to pull this off with a couple of helper columns. In the attached example file, I've formatted the table range as a structured Excel table and added two helper columns as follows:

     

    Selected:
=SUBTOTAL(103, [@[Tester Status]])

Shared:
=SUM([Selected]*ISNUMBER(FIND([@[Tester Status]], [Change Tester Statuses Enabled])))=SUM([Selected])

     

    Then, the formula used in the "Shared Parameters" column is:

     

    =TEXTJOIN(CHAR(10), TRUE, FILTER([Tester Status], [Shared], ""))

     

     

    Note: apply Wrap Text formatting to the "Shared Parameters" column in order to display the results on multiple lines.

     

    Also: the FIND function was used instead of SEARCH or COUNTIFS because FIND is case sensitive, so "Drafted" will not incorrectly be found within "Undrafted", for example.

Resources