# Excel - COLUMN Function Problem - Expert Solution

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
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
Excelchat Expert 18/03/2018 - 07:31
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

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.