Contains an Exact Match

Paul H

Problem is double results using the contains function

=JOIN(COLLECT({ECN Num}, {ECN Item}, CONTAINS(Item@row, @cell)), " ")

{ECN Item} cell is a list of affected items numbers

If the ECN Item I am looking for is "1234" I also get the results for item number "1234-05"

Any advice?

Paul Newcome

I didn't realize you could have multiple entries in a single cell. Sorry about that. it looks like you are not using a multi-select dropdown, so will it always be a line break immediately after the 1234 if it is strictly that and you want to exclude those that have a hyphen?

If you can rely on having a line break after 1234 in those that you want to grab, you can include that in your CONTAINS function.

=JOIN(COLLECT([ECN#]:[ECN#], [Affected Items]:[Affected Items], CONTAINS(Item@row + CHAR(10), @cell)), " ")

Basically this is looking for "1234_line_break" which will exclude "1234_hyphen"

Paul Newcome

Try something like this...

=JOIN(COLLECT({ECN Num}, {ECN Item}, @cell = Item@row), " ")

Paul H

The {ECN Item} cell can contain from one to twenty items, if I used "@cell = Item@row" it would skip any cell with multiple entries wouldn't it?

Paul H

I made a mini test sheet rather than the big crossref database, I am trying to pick out the only the ECN's that affect item 1234

This is the formula in Sheet summary

=JOIN(COLLECT([ECN#]:[ECN#], [Affected Items]:[Affected Items], CONTAINS("1234", @cell)), " ")

Paul Newcome

I didn't realize you could have multiple entries in a single cell. Sorry about that. it looks like you are not using a multi-select dropdown, so will it always be a line break immediately after the 1234 if it is strictly that and you want to exclude those that have a hyphen?

If you can rely on having a line break after 1234 in those that you want to grab, you can include that in your CONTAINS function.

=JOIN(COLLECT([ECN#]:[ECN#], [Affected Items]:[Affected Items], CONTAINS(Item@row + CHAR(10), @cell)), " ")

Basically this is looking for "1234_line_break" which will exclude "1234_hyphen"

Paul H

Thanks Paul, I might have found a away to make this work using the line break as you suggest

Problem: Line break does not work if there is only one affected item and occasionally people have used commas, slashes, or even brackets when entering affected items via the form. With over 13k entries its too much work to go back and fix them all.

So a helper column to clean up data and then add a final char(10) + "!" for those single items

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Affected Items]@row, ")", CHAR(10)), "(", CHAR(10)), ",", CHAR(10)), "/", CHAR(10)) + CHAR(10) + "!"

Paul Newcome

Yes. That should do the trick to swap out the invalid delimiters and also account for a single entry of 1234.