Forum Discussion
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....
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.