Finding unique values from Column, then listing them in another sheet

Frank S.

Here is my problem:

I have a column that has multiple different dates listed. I want a formula that will allow me to find the unique dates and then list just the unique dates in a different column.

I have done a lot of research and I know I need a helper column but I'm struggling with the examples I have found.

Any suggestions or help would be appreciated.

Leibel S

@Frank.Smith

Looks like I was missing a parentheses in my formula.

The below should be correct:

=IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Approved Change Requests Range 2}, {ONE Program Approved Change Requests Range 2}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A"))))), [Row #]@row), "")

Leibel S

see below

 =IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, AND(ISDATE(@cell), @cell <= TODAY()))), [Row #]@row), "")

Leibel S

yes

Mark Cronk

Try: =JOIN(DISTINCT(COLLECT([date]:[date], [date]:[date], ISDATE(@cell), char(10))))

Replace [date] with the name of your date column.

Work?

Mark

Frank S.

Mark,

I appreciate the help but I'm not sure I'm following you.

Do I include the formula in my original sheet or can I use it as a metric?

Here is what I have:

=JOIN(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, ISDATE(@cell), CHAR(10)))) and I get #Incorrect Argument

Suggestions?

Paul Newcome

The formula error itself is from a misplaced parenthesis.

=JOIN(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, ISDATE(@cell))), CHAR(10))

If you needed them listed out as dates, it will require additional helper columns and one of the parsing solutions. I will dig through my notes later this evening to see if I can find them. They are also out here somewhere in the Community if you wanted to do a search for them in the meantime.

Frank S.

Mark/Paul,

What I'm looking for is help setting up the helper column and then getting the dates to list.

I have look through the forums and have not been successful getting my helper column to work correctly.

Paul Newcome

HERE is a link to a sheet that contains a parsing solution. The columns to the left of the grey bar are notes, and the columns to the right of the grey bar are what you would actually set up. Does that help?

Leibel S

@Frank.Smith

you would need a column with a Row #. (you can manually create this by drag filling the numbers, create as many rows as you think you will need).

Then you would add the below as a column formula to another column.

=IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, ISDATE(@cell))), [Row #]@row), "")

Paul Newcome

@Leibel S Haven't seen you in a while. Hope all is well.

I completely forgot you had come up with that formula. I'm bookmarking this thread so I can come back to it. The only downside I can see to it is if you have a ton of rows the cross-sheet reference(s) could bog things down a little bit quicker, but overall I definitely like this approach better. Thanks for chiming in!

Leibel S

It has been awhile :). doing well thank god

Paul Newcome

@Leibel S Good deal. 👍️

Frank S.

@Leibel S That formula worked great.

Now if I wanted to check for a unique number what would change. The number I'm looking for is a build number that is typically something like 2.4.6

I tried is "isnumber" in place of "isdate" but it did not work.

Leibel S

@Frank.Smith

Because you have periods in your number you would need to substitute them out. The criteria that should work for this case would be:

AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))

Let me know how that goes :)

Frank S.

@Leibel S

Please forgive me, but I'm not sure where I would add the additional part of the formula.

Can you provide me with a little clarification.

Leibel S

@Frank.Smith

In you original post it seemed like you were trying to get a list of unique dates. Are you now looking to get a unique set of build numbers?

If yes, the full formula (adding to my previous post) would be:

=IFERROR(INDEX(DISTINCT(COLLECT({REFERENCE TO BUILD NUMBERS}, {REFERENCE TO BUILD NUMBERS}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))), [Row #]@row), "")

Make sure to replace the correct sheet reference.

Frank S.

@Leibel S

Here is the formula I'm using: =IFERROR(INDEX(DISTINCT(COLLECT({REFERENCE TO BUILD NUMBERS}, {REFERENCE TO BUILD NUMBERS}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))), [Row #]@row), "")) and I get #INCORRECT ARGUMENT SET

Suggestions?

Leibel S

You need to create the cross sheet references, as mentioned in my comment.