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.
