Create list of unique items without empty rows

Russ Suter
Russ Suter used Ask the Experts™
on
Using this formula, I can get an idea of which rows are distinct:
=IF(COUNTIFS($H$2:$H2,$H2)=1,"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?
Comment
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
Commented:
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

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

UNIQUE function
https://support.office.com/en-us/article/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e

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