Excel tutorials

How to display a list of unique results if two conditions are met, from two columns that contain duplicates

In this Excel tutorial, I will present two ways in which we can display as a result a list of unique names if two conditions are met, from two columns which contain duplicates in multiple rows of a cell range. In the picture below you can see the arrangement of data in the cell range. Columns 'A', 'B' and 'F' contain formulas. In columns 'C' and 'D' you enter the data manually. Our goal is to return a list of all unique data from 'D' column if two conditions match. The first condition of 'B' column and the second condition in the 'C' column. Columns 'B', 'C' and 'D' contain duplicate data.

The 'F2' and 'F5' cells contain the criteria that we are looking for. These cells contain the Data Validation Drop-Down menu. In the cell range 'F8:F11' you can see the expected results. There should be a list of unique data from the 'C' column.

Return a unique list if two conditions match

Date List without weekends and holidays

In the 'A' column, see Dates for a specific month. This date list does not include dates that belong to Saturday and Sunday as well as Dates that do not belong to the holiday season. In the 'A2' cell, enter the first date of the month manually.

In the 'A3' cell is the following formula. (this formula returns all dates that are not Saturdays, Sundays or Holidays. The cell range 'O2:O15' contains a list of dates for religious holidays and holidays, copy the formula below).
=MIN(IF(WEEKDAY(A2+{1;2;3;4;5};2)<6;IF(ISNA(MATCH(A2+{1;2;3;4;5};O$2:O$15;0));{1;2;3;4;5})))+A2

Display the number of weeks based on a specific date

In the 'B2' cell is the formula below. (This formula returns the number of weeks for a specific date from the 'A' column. Data from column 'B' will be the first condition ('F2' cell), based on which we look for the result. Notice that the 'B' column contains duplicates. Copy the form below).
="Week "&WEEKNUM(A2;2)

The 'P' and 'Q' columns contain a list of unique data that I have used for the Data Validation drop-down menu.

ARRAY formula in 'P2' cell. (Returns a list of unique data from the 'B' column. This CSE formula should enter with Ctrl+Shift+Enter)
=IFERROR(INDEX($B$2:$B$15;MATCH(0;COUNTIF(P$1:P1;$B$2:$B$15);0));"")

ARRAY formula in 'Q2' cell. (Returns a list of unique data from the 'C' column. You can end this CSE formula with Ctrl+Shift+Enter)
=IFERROR(INDEX($C$2:$C$15;MATCH(0;COUNTIF(Q$1:Q1;$C$2:$C$15);0));"")

Return unique data if two conditions match from two columns containing duplicates

The first way to resolve this task can be solved by using the help column. In the picture below, notice that the column 'H' was used for the helper column.

List unique data using the help column

In the 'H2' cell is the following formula. (This formula based on multiple conditions returns 'TRUE' or 'FALSE'. It is the basic element we will use later in returning unique data. Notice that this formula compares two criteria with the data in 'B and C' columns and returns 'TRUE' for the first match, ie the duplicates are ignored).
=AND(B2=$F$2;C2=$F$5;COUNTIFS(B$2:$B2;$F$2;C$2:$C2;$F$5;D$2:$D2;D2)=1)

In the 'F8' cell is the next ARRAY formula. (This formula for each result 'TRUE' in the 'H' column returns the data from the 'D' column of the same Row).
=IFERROR(INDEX($D$2:$D$15;SMALL(IF($H$2:$H$15;ROW($D$2:$D$15)-1);ROW(1:1)));"")

Display a list of unique data without the helper column

In the picture below, you will notice the same results as in the previous case. Still, no helper column was used here. So the formula below returns a list of all unique data if two criteria are met.

The ARRAY formula in the 'F8' cell in the picture below is the following. (This CSE formula returns a list of all unique data from 'D' column if two conditions match, ie from 'F2' and 'F5' cells).
=IFERROR(INDEX($D$2:$D$15;MATCH(0;COUNTIF(F7:$F$7;$D$2:$D$15)+IF($C$2:$C$15<>$F$5;1;0)+IF($B$2:$B$15<>$F$2;1;0);0));"")

Return a list of unique names without using the helper column

Please note that you must correctly set absolute and relative addresses in Excel formulas, especially if you copy the formula.
Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.