I was reviewing a blog post from last year that listed duplicates in a range. I thought I could shorten the formula using the LET function.
You can check out the full post at the link below.
The image below has the revised formulas.
My original formula solution was in cell G2. This lists the duplicate codes from the list in the range A2:A11.
=UNIQUE(VSTACK(UNIQUE(A2:A11,,1),UNIQUE(A2:A11)),,1)Note that it uses the UNIQUE function three times, and the range A2:A11 twice. I thought the LET function might make the formula shorter. The revised formula is in cell G8.
=LET(u,UNIQUE,r,A2:A11,u(VSTACK(u(r,,1),u(r)),,1))The u variable is assigned to the UNIQUE function. This is a new feature that allows the LET variable to refer to a function. The r variable is assigned to the range. Then we rebuild the formula by replacing each of the three UNIQUE functions with u. The two range references are replaced by r.
The solution in cell G13 is from Rick Rothstein who is an Excel MVP. He shared an alternative solution in the last blog post.
He uses the LET function to capture the range in the a variable. Then he uses the FILTER function to display the duplicates using the COUNTIF function in the criteria.
=LET(a,A2:A11,UNIQUE(FILTER(a,COUNTIF(a,a)>1)))Whilst my solution has slightly fewer characters than Rick’s, his solution might be easier to follow as it uses the FILTER and COUNTIF functions which are more easily understood.
