Excel easily allows to extract a list of unique items and count them. Handy when you have to extract an order list from a BOM of a PCB for example 😉
When you have a list of items and you want to list and count the unique entries, follow these steps:
- Select ‘Data’ -> ‘Filter’ -> ‘Advanced filter’
- Select ‘Action’ -> ‘Copy to another location’
- Fill in ‘List Range’ to be the column containing the data
- Fill in ‘Copy to’ to be the target colums
- Enable the ‘unique records only’ checkbox
- Click OK
Then, to count the number of unique entries, do as follows:
- Next to the target colum, enter the following formula:
=COUNTIF(F:F,I3). This formula counts the number of times the text in cell I3 is encountered in the source list range.
- Expand the formula to the complete target column height.
And you’re done!