Create list of unique items without empty rows

Russ Suter
Russ Suter used Ask the Experts™
Using this formula, I can get an idea of which rows are distinct:

Open in new window

Using this formula I can create this:
Unique ListThe problem here is that it contains blank lines. What I really need is a list of distinct values without any spaces in between like this:
Condensed Unique ListHow can I do that?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
If you are trying to concatenate First Name and Last Name from column A and B for unique first and last name combinations in column D, try this...

Assuming your data is in the range A1:B9, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In D1
=IFERROR(INDEX($A$1:$A$9,SMALL(IF(FREQUENCY(MATCH($A$1:$A$9&$B$1:$B$9,$A$1:$A$9&$B$1:$B$9,0),ROW($A$1:$A$9))>0,ROW($A$1:$A$10)),ROWS(D$1:D1)))&" "&INDEX($B$1:$B$9,SMALL(IF(FREQUENCY(MATCH($A$1:$A$9&$B$1:$B$9,$A$1:$A$9&$B$1:$B$9,0),ROW($A$1:$A$9))>0,ROW($A$1:$A$10)),ROWS(D$1:D1))),"")

Open in new window

Confirm with Ctrl+Shift+Enter and copy it down until you get blank cells.
Ryan ChongSoftware Team Lead

if you're subscribed as Office Insider, you should able to use the new feature UNIQUE to get unique value list.

UNIQUE function

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial