Automatically update Unique names in a ranking sheet from a survey intake sheet

BrendonL

Hi All,

I am currently building a system to rank speakers. I am try to copy unique names from the survey sheet to the ranking sheet so that I can sort them by some statistics. However, I am having trouble figuring out an automation that correctly identifies the unique names in the survey sheet and also updates the ranking sheet automatically with said unique name. Any suggestions?

Paul Newcome

You will first need to establish the maximum number of unique speakers and then decide on a number greater than that so that you have a buffer included.

From there you would have a text/number column (called "Number" in this example) with the numbers one through whatever number you decided on manually entered going down the rows.

Then in the column you want the unique speaker names in, you would use something along the lines of:

=INDEX(DISTINCT(COLLECT({Source Sheet Speaker Column}, {Source Sheet Speaker Column}, @cell <> "")), Number@row)

BrendonL

What if I dont know what the maximum will be. The list of speakers grows over time. And would there be any other way of putting those numbers in automatically because inputting 600+ numbers in is a bit of a hassle

Paul Newcome

You can include a flag type column with a formula counting how many unique names are in the source and flagging rows whenever you get within a certain threshold. You would then create an automation to run daily (since they can't be set to trigger off of a field containing a cross sheet reference) to alert you if you are starting to get close. Let's say you wanted an alert if you were within 10 of how many you have already pre-populoated. That formula would look like this:

=IF(COUNT(DISTINCT(COLLECT({Source Sheet Speaker Column}, {Source Sheet Speaker Column}, @cell <> ""))) > MAX(Number:Number) - 10, 1)

BrendonL

@Paul Newcome

Hey Paul, so i trying to get your way to work. However, it is leaving this massive gap in my sheet with errors,

There are unique names before and after this gap of error and im not sure why it is like that. do you know what is going on here?

Paul Newcome

You can wrap your formula in an IFERROR statement to get it to output a blank until there is a name.

=IFERROR(current_formula, "")

BrendonL

There are values after the errors too. Shouldnt it replace the error cells above with the correct info? doesnt the fact that there is a error value there mean that it is reading the name from the source sheet and not somehow not printing it out correctly?

Paul Newcome

It is because your number column is out of order. Once you sort the Number column so that it is 1 at the top and the last number at the bottom, the errors will only be at the bottom of the list indicating there aren't enough distinct names to fill that far down.