There are many situations in which you may want to use logical criteria to filter or limit the values processed by the UNIQUE function. In this first worksheet, we have a list of values, some of which are duplicates. Notice the list also contains empty or blank cells. If I use the UNIQUE function on the data as-is, we do get unique values, but we also get a zero, since the source data contains empty cells. We need a way to exclude the empty cells from results. And this is a perfect job for the FILTER function. Now, we want to filter out empty cells before they get to UNIQUE, so I’ll start with FILTER. For the array argument, we want to use the source data in B5:B16. Now, for the include argument, we need to set up a logical expression to exclude cells without any content. To do this, I can use B5:B16 is not equal to an empty string. For empty string I use double quotes. When I enter the formula, we get the source data without empty cells. Now that I have a working FILTER formula, I can simply wrap the UNIQUE function around this formula. Inside UNIQUE, empty values have already been removed by FILTER. When I enter the formula, we get correct results. This is an example of nesting one function inside another. On the next worksheet, we have a list of colors and groups, and we want to extract unique values for each group, A and B. I’ll follow the same process as before. In cell E5, I’ll set up the FILTER function to filter on group A. Because we are working with dynamic arrays, I can use the value in E4 directly without locking the reference. When I enter the formula, we get only values associated with group A. Now as I did in the first example, I can now simply wrap the UNIQUE function around FILTER function, and we get a list of unique values for group A. To finish up, I’ll copy the working formula in E5 to G5. Notice I’m using the formula bar to keep cell references intact. Now I only need to adjust the logic to test for group B using the header in G4. When I enter the formula, we have just the unique colors that appear in group B.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
