List of Unique Values for Dashboard

SYSPK · 11/19/18

I'd like to get a list of unique values in a range. I'm finding a lot on the community about how to flag duplicates, but not sure how to get a list where each unique value appears in a cell. Screenshot below is what I'm looking for. My goal is to incorporate that into a dashboard: I want to see all unique values that have certain status ball colors.

Is this possible?

Thank you

Screen Shot 2018-11-19 at 5.42.20 PM.png

Andrée Starå · 11/19/18

Hi,

Try this.

Add a column called Duplicates (Checkbox Type) and add the below formula. It's referencing the cells 2-21 and looking for duplicates in the range. You could then collect the results with a JOIN function.

=IF(COUNTIF($[My Data]2:$[My Data]21; $[My Data]2) > 1; 1)

The same version but with the below changes for your and others convenience.

=IF(COUNTIF($[My Data]2:$[My Data]21, $[My Data]2) > 1, 1)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SYSPK · 11/19/18

Thanks Andree, but not sure this helps. How does the JOIN function tie in here to create each duplicate value as a unique cell?

Andrée Starå · 11/20/18

I misunderstood. The JOIN isn't needed.

You can still use the formula to get the unique values.

How do you want to show the information on the dashboard?

Best,

Andrée

SYSPK · 11/20/18

No problem

What I want to see specifically, is unique values whose status balls are red. I know how to get a helper column to show me all values whose status balls are only red. This gives me a list of duplicates. Not sure where to go from there.

Screen Shot 2018-11-20 at 10.50.34 AM.png

Andrée Starå · 11/20/18

Ok.

Would it work to show the information in a report on the dashboard?

SYSPK · 11/21/18

Hmm, not sure. I'd prefer to see the information updated in real time, because the status balls change daily.

Andrée Starå · 11/21/18

The information would still be updated in the same way, so that isn't an issue.

Ok?

Best,

Andrée

SYSPK · 11/27/18

Thanks- reports certainly help with showing info in real time. But they only list rows that meet a criteria; they do not give a summary of unique values. Is there a way to do that?

Andrée Starå · 11/27/18

Happy to help!

The summary has to be done in another sheet, but then that information could be selected in the report.

Would that work?

Best,

Andrée

SYSPK · 11/28/18

Yes certainly. But.. do you know how to obtain that summary of unique values?

Andrée Starå · 12/30/18

Hi,

Try this.

1.

Add a checkbox column named "Duplicate" with the formula below and then fill it down to all rows containing data. Change the row references to the correct row number.

=IF(COUNTIF([My Data]$2:[My Data]2; [My Data]2) > 1; 1)

The same version but with the below changes for your and others convenience.

=IF(COUNTIF([My Data]$2:[My Data]2, [My Data]2) > 1, 1)

2.

In the column named "What I Want to See in Dashboard" add the below formula on all rows with data.

=IF(AND(Duplicate@row = 0; Status@row = "Red"); [My Data]@row)

The same version but with the below changes for your and others convenience.

=IF(AND(Duplicate@row = 0, Status@row = "Red"), [My Data]@row)

3.

Create a report that reports against the sheet and add criteria in the What? section that checks if the "What I Want to See in Dashboard" is blank and exclude it. The report will only show the unique values now.

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

Happy Holidays & Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Jeff Zumaris · 11/19/19

I use something similar to this when I want to find Unique Values in a column and then stack them at the top of the screen without any gaps. This is useful if you are trying to run metrics off of the unique values which can change or have new values at any time.

Here is an example using a separate sheet to find the values

(you could do this in the sheet itself but depending on how many rows all the formulas can def slow the sheet down to a crawl and the actual updating of unique values will have a delay.. need more processing power I suppose)

In my source sheet I have to columns named 1 & 2

Column 1 Formula =0 + 1

So every row Equals 1

Column 2 Formula =COUNT([1]$1:[1]1)

So every row is +1 of the next number we want this to happen so that we have a unique identifier to run our VLOOKUP on.

Screenshot 1

Back to our calculation sheet we have Column called NumSeq (Number Sequence) this is will be used in the Vlookup and the sorting of our unique fields.

Column NumSeq

Make this column as long as you wish with the numbers ascending 1-500..

Next up is the Value of the source sheet

Column Value Formula =IFERROR(VLOOKUP(NumSeq3, {Lookup Table}, Value$2), "")

We are telling the VLOOKUP to look at the value in NumSeq, reference the Lookup Table. Value$2 is the column number I want to reference in the source sheet which is 13 incase anybody wants to know. I wrap this in an IFERROR because I don't want values of #NO MATCH

Column Duplicate Formula (Checkbox) =IFERROR(IF(COUNTIF(Value$3:Value3, Value3) > 1, 1), "")

This is going to look for duplicate values in the Value column. Again wrapped in an IFERROR to avoid breaking.

Column ValueA Formula =IF(Duplicate3 = false, Value3)

This is looking at our checkbox column and saying if the value is False then give me the Value in the ValueA Cell,

ZZ-A Formula (No idea what i named it this) =IF(NOT(ISBLANK(ValuesA3)), 1)

This is saying if there is a Value in ValueA give it a value of "1"

Column SUM-A Formula =[SUM-A]2 + [ZZ-A]3

This is saying Add the cell next to me + the one on top of me. More Simply if we have a Unique Value we get a 1 and if we have Duplicates we don't add anything.

Column UniqueA Formula =IFERROR(INDEX(ValuesA:ValuesA, MATCH($NumSeq3, [SUM-A]:[SUM-A], 0)), "")

Finally we are saying with this INDEX MATCH is Look in ValuesA:ValuesA and Match NumSeq number agains SUM-A:SUM-A. Again wrapped in an IFERROR for the reasons stated above.

This will take all the values in SUM-A that are spaced out and stack them nice and neat at the top of the column.

I know this is probably super confusing but i'll add some screen shots to see if it helps.

Jana Brits · 05/08/20

Hey

Did you anyone managed to help you with this question?

Jana

SYSPK · 05/17/20

@Jana Brits Yes I used a helper column like the My Data column I put above, then 2 helper columns next to it:

Helper1: =IF(COUNTIF(MyData$1:MyData@row, MyData@row) <> 1, 0, 1)

Helper2: =IF([Helper1]@row = 1, MyData@row, "")

My report pulls rows where Helper2 is not blank or invalid, and where the status = red.

Paul Grim · 05/28/20

Is there a way to do this without resorting to using a helper column? In other words, is there a formula you can use on one sheet that will return a count of each unique value from a column on another sheet? The formula would need to be dynamic, accommodating new values as they are added to the source sheet.

I need to obtain a count of each instance of a value in a particular column. For example, "How many tasks are in each status?" where the result might be something like:

Pending = 15

In Progress = 9

On Hold = 4

Complete = 7

I don't see a way to do this other than with helper columns (or in the Sheet Summary, which isn't dynamic), unless I'm missing something?

List of Unique Values for Dashboard

Answers

Help Article Resources

Categories