Need to extract Non-duplicate values

Gajapathi Muniyappa

Hi, I'm in the need of extracting the Non-duplicate values in a range. I could able to achieve same in the excel (Formula =UNIQUE(A2:A16)). See the attached excel schreenshot. I knew Distinct function is a replacement of unique in smartsheet but it can be used within the other function and not working propery. Kindly suggest same to achieve in the smartsheet.

Unique.PNG

Genevieve P.

Hi @Gajapathi Muniyappa

Can you explain what your end-goal is with the list?

The easiest way to do this would be to create a Row Report and then Group the Report by the country column. This will automatically organize your data into one unique list, with duplicate rows grouped beneath each heading. See: Configure grouping to organize results in report builder

Another quick way to list all values would be to use a JOIN formula to bring all unique values into one multi-select cell. This wouldn't be a list down multiple rows, but instead a list in one cell.

=JOIN([Column Name]:[Column Name], CHAR(10))

If you need the values down one column, you'll need a helper column with numbers to indicate which of the distinct values to bring back. Then you can use an INDEX(DISTINCT combination to bring back only distinct countries, like so:

=IFERROR(INDEX(DISTINCT([Countries with Duplicates]:[Countries with Duplicates]), [Row Number]@row), "")

You can set this up in a separate sheet, if you'd like. In this instance you'd use a {cross sheet reference} to highlight the country column, like so:

=IFERROR(INDEX(DISTINCT({Countries with Duplicates}), [Row Number]@row), "")

See: Create cross sheet references to work with data in another sheet

Cheers!

Genevieve

Paul Newcome

@Gajapathi Muniyappa Try an

=IFERROR(INDEX(DISTINCT(COLLECT({Status}, {Sponsor}, @cell = Sponsor@row)), Number@row), "")

Gajapathi Muniyappa

I knew it can be achieved via smartsheet pivot but I need it via formula or any other method.

Genevieve P.

Hi @Gajapathi Muniyappa

Can you explain what your end-goal is with the list?

The easiest way to do this would be to create a Row Report and then Group the Report by the country column. This will automatically organize your data into one unique list, with duplicate rows grouped beneath each heading. See: Configure grouping to organize results in report builder

Another quick way to list all values would be to use a JOIN formula to bring all unique values into one multi-select cell. This wouldn't be a list down multiple rows, but instead a list in one cell.

=JOIN([Column Name]:[Column Name], CHAR(10))

If you need the values down one column, you'll need a helper column with numbers to indicate which of the distinct values to bring back. Then you can use an INDEX(DISTINCT combination to bring back only distinct countries, like so:

=IFERROR(INDEX(DISTINCT([Countries with Duplicates]:[Countries with Duplicates]), [Row Number]@row), "")

You can set this up in a separate sheet, if you'd like. In this instance you'd use a {cross sheet reference} to highlight the country column, like so:

=IFERROR(INDEX(DISTINCT({Countries with Duplicates}), [Row Number]@row), "")

See: Create cross sheet references to work with data in another sheet

Cheers!

Genevieve

Gajapathi Muniyappa

Hey Genevieve,

Great! It helps lot to achieve my target. Thank you so much :)

Gajapathi Muniyappa

Hi @Genevieve P. @Paul Newcome

I'm in the need of same formula with additional match criteria, see the screenshot. I need to add the additional match criteria for sponsor, need all list down unique statuses pertains to spnsor - AstraZeneca. Could you please help me out from this.

Paul Newcome

@Gajapathi Muniyappa What is the end use for this data?

Gajapathi Muniyappa

@Paul Newcome Need to extract the non duplicate values with given criteria. Need to extracts the dynamic summaries. If the above said case, sponsor is a dropdown menu, so I can swap any sponsor, so that formula will get the related statuses to that particular sponsor. so that I can get the project count on particular status.

Paul Newcome

@Gajapathi Muniyappa

If you just need the count for each status based on a specific sponsor, you can create a report that is filtered to show only those rows that have the sponsor in question, group the report by the Status column, then include a count summary in the report.

Gajapathi Muniyappa

@Paul Newcome I'm very much familiar with the above answer but I need to change the sponsor dynamically to show the impact on all summaries/diagrams in the dashboard by one click. This can be achieve only formulas. Each time when I want to change the sponsor filter, I can't change it in the all reports assosciated to dashboard. We have more than more sponsors and I can't create multiple dashboards for each sposnor.

Paul Newcome

@Gajapathi Muniyappa Try an

=IFERROR(INDEX(DISTINCT(COLLECT({Status}, {Sponsor}, @cell = Sponsor@row)), Number@row), "")

Gajapathi Muniyappa

Great! Thanks Paul, It worked as I expected.