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

K1600

Board Regular
Joined
Oct 20, 2017
Messages
190
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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
Excel Formula:
=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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How about
Excel Formula:
=UNIQUE(filter(Table_Std[Column1],C2=Table_Std[Start Date],""))
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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
Excel Formula:
=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.
 
Upvote 0
How about
Excel Formula:
=UNIQUE(filter(Table_Std[Column1],(C2=Table_Std[Start Date])*([Course Cancelled]=""),""))
 
Upvote 0
Solution

Forum statistics

Threads
1,225,727
Messages
6,186,686
Members
453,368
Latest member
xxtanka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top