Excel 2020: One Hit Wonders with UNIQUE
September 28, 2020 - by Bill Jelen
For me, I can't imagine why I would ever need a list of items that have been sold exactly once. My only example is the One-Hit Wonders segment on Casey Kasem's American Top 40 radio show.
To get a list of artists who had exactly one hit, use =UNIQUE(B4:B6132..True)
. In the figure below, the UNIQUE function is wrapped in a SORT function so the resulting list is alphabetical.
To get the titles in column J, a VLOOKUP uses an array as the first argument. This is pretty wild - one VLOOKUP formula is actually doing over 1000 lookups and returning all 1000 results.
Another approach is to use a FILTER function combined with IFERROR and MATCH.
Title Photo: Eric Nopanen at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.