Unique Filter a Range based on Multiple Criteria

[TerryE]

Hi
I am usually quite competent using the Filter function in my work. However, this one seems to be more challenging than I thought it would be.
We have training records for all staff and when chasing up course completions we want to avoid anyone who is in training or long term absent and I have managed to compile a list of names and the course not completed that does not include those staff
However, I am asked to provide a list of names that fall in that exclusion criteria on a separate tab

So from the attached file which has the same name repeated as they have multiple courses outstanding, I just want one row per name, regardless of how many courses they have outstanding and as long as they have Yes in "Training" or "LTA" -

Name	Email	Start Date	Manager	Office	Trainee	LTA
John Smith	Jsmith@work.com	01/01/2026	Jas Defoine	Kent	Yes
Martha Logan	Mlogan@work.com	05/06/2014	Jas Defoine	Bristol		Yes
Tony Letts	Tletts@work.com	01/01/2026	Jack Beatty	Manchester	Yes

I have tried various attempts with unique and filter and choosecols but do not know how to include the whole range, excluding the course details, but only apply the unique to the Name column?
Thanks in advance for any help you can give
Regards
Terry
PS the attached is Excel 2013, which is all I have on my home laptop. The file I use is on my works laptop using Office 365

 

[AlanSidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Trainee]="Yes" or [LTA]="Yes" then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Custom"})
in
    #"Removed Columns"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[MikeVol]

With Power Query

Or use vba macro:

Option Explicit

Sub Evaluate_Unique()
    Dim i           As Long
    On Error GoTo CleanExit
    Application.ScreenUpdating = False

    With ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

        Dim rng     As Range
        Set rng = .DataBodyRange
        rng.Columns(.ListColumns("Start Date").Index).Value = _
                Evaluate("IF(ISNUMBER(" & _
                rng.Columns(.ListColumns("Start Date").Index).Address & "), " & _
                rng.Columns(.ListColumns("Start Date").Index).Address & ", """")")

        Dim arr     As Variant
        arr = Evaluate("(" & _
                rng.Columns(.ListColumns("Trainee").Index).Address & "=""" & "Yes" & """)+" & _
                "(" & rng.Columns(.ListColumns("LTA").Index).Address & "=""" & "Yes" & """)")

        For i = UBound(arr, 1) To 1 Step -1
            If arr(i, 1) = 0 Then .ListRows(i).Delete
        Next i

        .Range.RemoveDuplicates _
                Columns:=.ListColumns("Name").Index, Header:=xlYes
    End With

CleanExit:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox Err.Description, vbCritical
    End If

End Sub

 

[Debaser]

Perhaps:

=VSTACK({"Name","Email","Start date","Manager","office"},UNIQUE(FILTER(CHOOSECOLS(B5:J16,1,2,3,6,7),(I5:I16="Yes")+(J5:J16="Yes"))))

 

[TerryE]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Trainee]="Yes" or [LTA]="Yes" then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Custom"})
in
    #"Removed Columns"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

Hi
I am usually quite competent using the Filter function in my work. However, this one seems to be more challenging than I thought it would be.
We have training records for all staff and when chasing up course completions we want to avoid anyone who is in training or long term absent and I have managed to compile a list of names and the course not completed that does not include those staff
However, I am asked to provide a list of names that fall in that exclusion criteria on a separate tab

So from the attached file which has the same name repeated as they have multiple courses outstanding, I just want one row per name, regardless of how many courses they have outstanding and as long as they have Yes in "Training" or "LTA" -

Name	Email	Start Date	Manager	Office	Trainee	LTA
John Smith	Jsmith@work.com	01/01/2026	Jas Defoine	Kent	Yes
Martha Logan	Mlogan@work.com	05/06/2014	Jas Defoine	Bristol		Yes
Tony Letts	Tletts@work.com	01/01/2026	Jack Beatty	Manchester	Yes

I have tried various attempts with unique and filter and choosecols but do not know how to include the whole range, excluding the course details, but only apply the unique to the Name column?
Thanks in advance for any help you can give
Regards
Terry
PS the attached is Excel 2013, which is all I have on my home laptop. The file I use is on my works laptop using Office 365

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Trainee]="Yes" or [LTA]="Yes" then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Custom"})
in
    #"Removed Columns"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

Hi Alan
thanks for your reply and guidance.
For now, I am going to take the option provided using the formulas I am more used to
Regards
Terry

 

[TerryE]

Or use vba macro:

Option Explicit

Sub Evaluate_Unique()
    Dim i           As Long
    On Error GoTo CleanExit
    Application.ScreenUpdating = False

    With ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")

        Dim rng     As Range
        Set rng = .DataBodyRange
        rng.Columns(.ListColumns("Start Date").Index).Value = _
                Evaluate("IF(ISNUMBER(" & _
                rng.Columns(.ListColumns("Start Date").Index).Address & "), " & _
                rng.Columns(.ListColumns("Start Date").Index).Address & ", """")")

        Dim arr     As Variant
        arr = Evaluate("(" & _
                rng.Columns(.ListColumns("Trainee").Index).Address & "=""" & "Yes" & """)+" & _
                "(" & rng.Columns(.ListColumns("LTA").Index).Address & "=""" & "Yes" & """)")

        For i = UBound(arr, 1) To 1 Step -1
            If arr(i, 1) = 0 Then .ListRows(i).Delete
        Next i

        .Range.RemoveDuplicates _
                Columns:=.ListColumns("Name").Index, Header:=xlYes
    End With

CleanExit:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox Err.Description, vbCritical
    End If

End Sub

Hi Alan
thanks for your reply and guidance.
For now, I am going to take the option provided using the formulas I am more used to
Furthermore, the company does not allow macros on its platform which is a shame as I do like vba and its capabilities.
Regards
Terry

 

[TerryE]

Perhaps:

=VSTACK({"Name","Email","Start date","Manager","office"},UNIQUE(FILTER(CHOOSECOLS(B5:J16,1,2,3,6,7),(I5:I16="Yes")+(J5:J16="Yes"))))

This is just what I am looking for, Debaser, many thanks.
I had cobbled together several variations of your solution but never managed to resolve it as you have
Thanks for your help
regards
Terry