Скачать или смотреть How to Filter a Unique List in Excel While Searching for Specific Text

Learn how to combine the UNIQUE and FILTER functions in Excel to effectively create a unique list that meets specific criteria. Get rid of null values easily!
---
This video is based on the question https://stackoverflow.com/q/71575011/ asked by the user 'Meghan Perry' ( https://stackoverflow.com/u/18543677/ ) and on the answer https://stackoverflow.com/a/71589405/ provided by the user 'Rory' ( https://stackoverflow.com/u/3611989/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to filter a unique list if the UNIQUE formula is also searching for specific text

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Filter a Unique List in Excel While Searching for Specific Text

In Excel, combining functions can often lead to more powerful and efficient formulas. If you're trying to filter a unique list based on specific text criteria, you might find it challenging to do so without creating additional helper columns. Let's delve into how to efficiently achieve this using the UNIQUE, FILTER, and SEARCH functions in Excel.

The Problem

You may already be familiar with using the UNIQUE function to extract unique values from a list. However, when you want to filter this list to include only those items that meet specific conditions (like containing a certain substring), it doesn't work as straightforwardly as you might hope.

For instance, suppose you're managing course data, and you'd like to generate a unique list of course IDs associated with Spanish classes. You previously leveraged a helper column to avoid null values. The challenge arises when you try to remove that helper column and still maintain a clean output without including any false entries in your results.

The Solution

To create a unique list that excludes null entries while retrieving values based on specific text criteria, you'll want to use a combination of the FILTER and UNIQUE functions. Here's how:

Step-by-Step Approach

Identify Your Data:

Assume your course data is in column G (course names) and the corresponding IDs are in column F.

Use the SEARCH Function:

Utilize the SEARCH function to locate the keyword "SPAN" within your course names.

Combine with ISNUMBER:

The ISNUMBER function will convert the results from SEARCH into Boolean values (TRUE/FALSE). This helps in filtering only relevant entries.

Apply the FILTER Function:

The FILTER function will further refine your list by excluding any empty or unwanted values.

Integrate UNIQUE and SORT:

Finally, wrap the entire formula with UNIQUE to ensure all results are distinct, and you can use SORT to order them.

The Final Formula

Combine these functions into one coherent formula:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Formula

FILTER('reqCH'!F:F, ISNUMBER(SEARCH("SPAN", 'reqCH'!G:G))): This part of the formula filters the course IDs in column F based on whether the corresponding entry in column G contains the substring "SPAN".

UNIQUE(...): Ensures that the list of course IDs returned is unique, eliminating duplicates.

SORT(...): Sorts the unique list in ascending order for better readability.

Conclusion

Using the above approach, you can effectively create a unique list of course IDs filtered by specific criteria, all within a single formula. This method helps eliminate the need for helper columns while maintaining a clean, manageable Excel sheet.

Embrace these Excel formulas to save time and enhance your data analysis capabilities! Whether you're working with simple lists or complex datasets, mastering functions like UNIQUE and FILTER can make a significant difference.