One Hit Wonders with UNIQUE - KING OF EXCEL

Sunday, January 19, 2020

Home Mr King Excel Tips One Hit Wonders with UNIQUE

One Hit Wonders with UNIQUE

January 19, 2020 Mr King Excel Tips,

One Hit Wonders with UNIQUE

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)).

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.

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.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Crafted with by TemplatesYard