Unique Advanced Filtering

Advanced filtering in Excel is in fact nothing new in recent years, but it is a very useful feature, worth revisiting and reminding its role in data analysis and presentation. In the following example I demonstrate how you can filter the selected data (a list in this case) to show the unique names/IDs, based on just one simple criteria. Not only that; at the same time you can get an additional information - number of times each of the unique names/IDs appears in the list. Here's the setup and the result:

To COUNT any Characters or Strings in any Range of Cells

Use  an ARRAY formula. You can find out the number of occurrences of anything (e.g. BS, 2016, s, graph, three days, 50%, etc.) within a given cell or a range of cells.

Assuming that your data range is e.g. A2:D100, select any cell outside that range and type the formula similar to this one:

 =SUM((LEN(A2:D100)-LEN(SUBSTITUTE(A2:D100,"Fig.","")))/LEN("Fig."))

and simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets. And the cell where you array-entered the formula will show the result, like here in cell G1: