Use 'Unique', 'Filter' and 'If' statements.

[K1600]

I am trying to pull unique values from a table, based on a date criteria but not putting blanks in the results.

My table (Table_Std) has a number of columns, but the relevant ones here are B and C. B (Column1) is a course code which is want to pull out, and C (Start Date) is the course start date. The course code and the dates will be duplicated, hence the need for the 'unique'.

On the second sheet (Glynn Blank), there is a list of dates across the top (row 2) and I am wanting to get a list of the courses running each day. Using

=UNIQUE(IF('Glynn - Blank'!C2=Table_Std[Start Date],Table_Std[Column1],""))

returns the correct course codes but there are blank cells between the course codes which I need to get rid of as I just want them all in a single list.

If I then use

=UNIQUE(FILTER(C23#,C23#<>""))

, where C23# is the range of the results from the first formula above, I get exactly what I need, but I cannot work out how to combine the formula's so I don't need to use an additional column for each one.

Can anyone tell me how I could combine the above two to use as a single formula (or another solution if that is better)

Hopefully that makes sense. Thanks in advance.

 

[Fluff]

How about

=UNIQUE(filter(Table_Std[Column1],C2=Table_Std[Start Date],""))

 

[K1600]

How about

=UNIQUE(filter(Table_Std[Column1],C2=Table_Std[Start Date],""))

Absolutely spot on as usual.

Thank you kindly!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[K1600]

You're welcome & thanks for the feedback.

Can I be a pain and see if we can tweak it slightly.

One of the columns in the table, is for if the course has been cancelled (a 'Yes' is added if cancelled but nothing is added if it goes ahead). Is there a way of stopping the returned list including the courses that have been cancelled within the one formula?

Although I woiuld prefer not to, I don't mind if I have to remove the course code by using something like

=IF([@[Course Cancelled]]="Yes","",CONCATENATE("(",[@[Course No.]],") Std"))

in the Course Code column (Column1) but if I do this, I end up with gaps in my returned list again.

Thanks.

 

[Fluff]

How about

=UNIQUE(filter(Table_Std[Column1],(C2=Table_Std[Start Date])*([Course Cancelled]=""),""))

 

[K1600]

How about

=UNIQUE(filter(Table_Std[Column1],(C2=Table_Std[Start Date])*([Course Cancelled]=""),""))

Perfect again.

Thank you.

 

[Fluff]

My pleasure.