Excel 2024: One Hit Wonders with UNIQUE
August 08, 2024 - by Bill Jelen
![Excel 2024: One Hit Wonders with UNIQUE Excel 2024: One Hit Wonders with UNIQUE](/img/excel-tips/2024/08/excel-2024-one-hit-wonders-with-unique.jpg)
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.
![By using TRUE for the Occurs_Once argument in UNIQUE, you can find the artists who only appeared once in the original database. Sort those alphabetically using =SORT(UNIQUE(B4:B6132,False,True)).](/img/content/2024/08/LXFig-110.jpg)
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.
![To get the title next to the artist, use =VLOOKUP(I5#,B4:C6132,2,False). You know that these one-hit-wonder artists only appear once in the database, so VLOOKUP works.](/img/content/2024/08/LXFig-112.jpg)
Another approach is to use a FILTER
function combined with IFERROR
and MATCH
.
![Use =FILTER(A4:D6132,IFERROR(MATCH(B4:B6132,UNIQUE(B4:B6132,False,True),0),False)) to return all four columns from the table.](/img/content/2024/08/LXFig-113.jpg)
This article is an excerpt from MrExcel 2024 Igniting Excel