How to extract the data from the another sheet?

Peter Vo

I am trying to find out the formula that help me to list out how many trading partners that we are currently working on it.

The list should not be duplicated and it will be Renault, Titan X, Volvo. Can someone please help me?

Genevieve P.

Hey @Peter Vo

If you're using COLLECT to filter down your range, you'll actually want to add DISTINCT before COLLECT, like so:

=JOIN(DISTINCT(COLLECT({TrueAuto ALL Teams Range 19}, {TrueAuto ALL Teams Rnage4}, "Cabarrus")), ", ")

Make sure the end of your formula closes like this:

)), ", ")

Since you don't close off the DISTINCT( and COLLECT( until the very end. Let me know if that works!

Cheers,

Genevieve

Peter Vo

hi Genevieve P.

Awesome! You are the best!

Andrée Starå

Hi @Peter Vo

I hope you're well and safe!

Have you explored using a report instead?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Peter Vo

hi @Andrée Starå ,

Yes, i know how to use report but i want to make it on Sheet also. is there any way to do it? Thanks!

Genevieve P.

Hi @Peter Vo

You can use the DISTINCT function to identify how many distinct values are in a column.

=COUNT(DISTINCT([Column Name]:[Column Name]))

So in your case:

=COUNT(DISTINCT([Trading Partner]:[Trading Partner]))

If you need to know what those distinct values are, you can use a JOIN(DISTINCT combination, like so:

=JOIN(DISTINCT([Trading Partner]:[Trading Partner]), ", ")

Cheers,

Genevieve

Peter Vo

hi @Genevieve P.,

Excellent! That's working. Now, i would like to add Collect with JOIN. But, it is not working again. Can you please advise? I want to extract the value with condition for Cabarrus division only. Thanks!

Genevieve P.

Hey @Peter Vo

If you're using COLLECT to filter down your range, you'll actually want to add DISTINCT before COLLECT, like so:

=JOIN(DISTINCT(COLLECT({TrueAuto ALL Teams Range 19}, {TrueAuto ALL Teams Rnage4}, "Cabarrus")), ", ")

Make sure the end of your formula closes like this:

)), ", ")

Since you don't close off the DISTINCT( and COLLECT( until the very end. Let me know if that works!

Cheers,

Genevieve

Peter Vo

hi Genevieve P.

Awesome! You are the best!