**Question description:**

*This user has given permission to use the problem statement for this blog.*

" list all the unique names in a column except for blank cells "

Solved by A. H. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
18/03/2018 - 07:13

Helloo..

User
18/03/2018 - 07:13

hi

Excelchat Expert
18/03/2018 - 07:14

If i understand the question correctly....

User
18/03/2018 - 07:14

can you make me a formula that lists the unique names in a column between say A1 and A100 without returning a result for the blank cells?

Excelchat Expert
18/03/2018 - 07:14

you need to extract unique names from the list..

User
18/03/2018 - 07:14

yes but "ignore the blanks"

Excelchat Expert
18/03/2018 - 07:15

sure... if you have a sample..i can show the result..

User
18/03/2018 - 07:15

please try this

User
18/03/2018 - 07:15

so if you can put it in another column the answer

Excelchat Expert
18/03/2018 - 07:16

ok...

User
18/03/2018 - 07:16

btw i use excel

Excelchat Expert
18/03/2018 - 07:17

the formula might remain the same..

Excelchat Expert
18/03/2018 - 07:17

lets try...

Excelchat Expert
18/03/2018 - 07:21

can you check this?

User
18/03/2018 - 07:21

ok ill look

User
18/03/2018 - 07:22

now i will try this in excel and see if it gives me a zero to represent the blank spaces

User
18/03/2018 - 07:22

one moment

Excelchat Expert
18/03/2018 - 07:22

ok

User
18/03/2018 - 07:25

sorry but in excel it returns a zero

Excelchat Expert
18/03/2018 - 07:25

did some tweek... try this instead..

Excelchat Expert
18/03/2018 - 07:25

=IFERROR(INDEX($A$1:$A$28, MATCH(0, COUNTIF($C$1:C1, $A$1:$A$28&"") + IF($A$1:$A$28="",1,0), 0)), "")

Excelchat Expert
18/03/2018 - 07:26

this is an array formula.. and you will need to use CTRL+SHIFT+ENTER

User
18/03/2018 - 07:26

ok hang on

User
18/03/2018 - 07:27

no good

User
18/03/2018 - 07:27

hen i drag down it repeats the first name

User
18/03/2018 - 07:27

wait

User
18/03/2018 - 07:28

ok wait it works and there is no zero but thats because its only returning the name house

User
18/03/2018 - 07:28

in every cell

Excelchat Expert
18/03/2018 - 07:29

in the sample i'm working its returning.. only unique values..

Excelchat Expert
18/03/2018 - 07:29

and no 0 for blanks..

Excelchat Expert
18/03/2018 - 07:29

are you adding this as an array formula?

User
18/03/2018 - 07:30

r u doing in excel

Excelchat Expert
18/03/2018 - 07:30

yes.. i'm sampling this on excel 2013

User
18/03/2018 - 07:30

wait ill hand type into other pc where it will need to go

User
18/03/2018 - 07:30

one moment please

Excelchat Expert
18/03/2018 - 07:31

[Uploaded an Excel file]

Excelchat Expert
18/03/2018 - 07:32

you could check the excel i've attached aswell..

User
18/03/2018 - 07:36

ok thanks i will i just tried typing and have to look for typo please excuse I'm not professional

User
18/03/2018 - 07:36

one more momnet

Excelchat Expert
18/03/2018 - 07:36

ohh,.. ok..

User
18/03/2018 - 07:36

moment..lol

User
18/03/2018 - 07:40

i typed it correctly and it gives me zeros

Excelchat Expert
18/03/2018 - 07:40

have you looked at the attached excel?

User
18/03/2018 - 07:40

what is column B and C

User
18/03/2018 - 07:41

which one is the list of unique names

Excelchat Expert
18/03/2018 - 07:41

column B has the initial values...

Excelchat Expert
18/03/2018 - 07:41

*formula..

User
18/03/2018 - 07:41

what values

Excelchat Expert
18/03/2018 - 07:41

column C has the newer formula withour getting 0

User
18/03/2018 - 07:41

the list of unique names

Excelchat Expert
18/03/2018 - 07:41

yes..

User
18/03/2018 - 07:41

why does B get zero and c not

Excelchat Expert
18/03/2018 - 07:42

yes.. B is giving 0 because of the blank cell..

User
18/03/2018 - 07:42

ok so C is the one i want?

Excelchat Expert
18/03/2018 - 07:42

while C has the formula updated to ignore blank..

Excelchat Expert
18/03/2018 - 07:42

yes.. c is the one which you will need

User
18/03/2018 - 07:42

ok ill try pasting it in hang on

Excelchat Expert
18/03/2018 - 07:42

hmm.. ok

Excelchat Expert
18/03/2018 - 07:45

we are almost coming to the end of session

Excelchat Expert
18/03/2018 - 07:45

can u confirm if this works...

Excelchat Expert
18/03/2018 - 07:48

can you please confirm..

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*