Extract List of Unique Values

Neil Watson

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?

Paul Newcome

@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.

Paul Newcome

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.

Paul Newcome

I'm not sure if there is a way to do it through Bridge, but I know it can be done with formulas.

Neil Watson

@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.

Paul Newcome

@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.

Neil Watson

Hey @Paul Newcome thanks.

I should have mentioned I need the answer in a column format!

Paul Newcome

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.

Neil Watson

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.

Paul Newcome

Happy to help. 👍️

Paul Newcome

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.