Extract List of Unique Values
Hi
Is it possible to use Bridge to extract from a column a list of values, run a JS to extract an array of unique values, and then pass this to another sheet and update another column with this unique list?
Best Answers
-
@Neil Watson You should be able to use the LOWER function for evaluation but still output the version that includes caps.
=IFERROR(INDEX(DISTINCT(LOWER(COLLECT(.....................))), 1), "")
I haven't tested it yet, but I feel like it should work.
-
Right. There are already a number of posts here in the community detailing how to pull a distinct or unique list from another sheet via formula. It usually involves the formula above without the LOWER function. The number 1 would output the first distinct value. Changing that to a 2 will output the second distinct value, so on and so forth. I usually use a helper column with the numbers manually entered so I can apply it as a column formula.
Answers
-
I'm not sure if there is a way to do it through Bridge, but I know it can be done with formulas.
-
@Paul Newcome I will give the formula route a go. The use case is that we download a bunch of data from a system that has a free text field, and I want to look at the text column and in a different sheet extract a list where the duplicates are removed. Ideally convert the text to lowercase first, to cover the cases where caps are different.
-
@Neil Watson You should be able to use the LOWER function for evaluation but still output the version that includes caps.
=IFERROR(INDEX(DISTINCT(LOWER(COLLECT(.....................))), 1), "")
I haven't tested it yet, but I feel like it should work.
-
Hey @Paul Newcome thanks.
I should have mentioned I need the answer in a column format!
-
Right. There are already a number of posts here in the community detailing how to pull a distinct or unique list from another sheet via formula. It usually involves the formula above without the LOWER function. The number 1 would output the first distinct value. Changing that to a 2 will output the second distinct value, so on and so forth. I usually use a helper column with the numbers manually entered so I can apply it as a column formula.
-
Thanks @Paul Newcome for the advice.
I was not able to get the formula to work with the LOWER function, it appears not to work with COLLECT, but did the lower case conversion with a helper column so worked out.
-
Happy to help. 👍️
-
I hadn't had a chance to test the LOWER function yet, so I wasn't 100% sure if it would work or not. I just thought it should in theory. I am going to have a play with it at some point though out of my own curiosity.
