32

I would like to use spreadsheets to get all unique names from Column A in a table but in the same time I would like blank cells to be ignored. So far I've got this formula that returns all of the unique names from column A but I don't know how to go about ignoring blank cells and not repeating values that have once been added previously.

Here is how my document looks so far. As you can see everything stops after Megan because there is a blank cell.

=IFERROR(INDEX($A$2:$A$90, MATCH(0, COUNTIF($I$10:I10, $A$2:$A$90), 0)), "")

Searched long and wide but came up with nothing, if anyone has any idea how one could do that I would really appreciate it. Thanks!

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
DIzZy
  • 323
  • 1
  • 3
  • 4

4 Answers4

85

=unique(A2:A) should work

=unique(filter(A2:A,A2:A<>"")) to also ignore blanks

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • Thank you for your very quick reply but using your formula returns duplicate values as you can see here http://prntscr.com/gyhxmh – DIzZy Oct 17 '17 at 13:03
  • It works, my bad when trying to use it. Thank you very much!! – DIzZy Oct 17 '17 at 13:16
  • 1
    @Max Makhrov I used this method to remove a string that was my headers as well as ignore white space! I was curious if there was a name for the `<>` notation or if you could provide any further documentation. – ConstantFun Feb 05 '19 at 12:25
  • @ConstantFun, I cannot find the documentation for `<>`. This is known from Excel: https://stackoverflow.com/questions/532435/what-does-mean – Max Makhrov Feb 06 '19 at 10:28
  • 1
    unique is an excel beta function, what about any other formula which will with Excel 2019 or older stable version? – Junaid Feb 23 '19 at 15:25
  • 1
    NOT(ISBLANK(...)) also availabe (more readable) eg. FILTER(A2:B10, NOT(ISBLANK(A2:A10))) – Namo Jul 31 '19 at 03:41
  • `=unique(filter(A2:A,A2:A<>""))` removes any empty/blank row. `=unique(A2:A)` does NOT. I tested four values (strings and numbers separately) where there was one duplicate value and used the `ROWS` function to display the number of rows of data returned. `=rows(unique(filter(A2:A,A2:A<>"")))` returned a value of 3 (no empty row) whereas `=rows(unique(A2:A))` returns a value of 4 (i.e. it includes the empty row). – Tedinoz Nov 25 '21 at 21:26
23

Yet another hack

=SORT(UNIQUE(A2:A))

Technically, this does not remove the blank result. But nonetheless puts it at the end of the list. You'll also benefit from the sort if you need it.

Majal
  • 1,635
  • 20
  • 31
6

You can use query:

=unique(query(A2:A,"select A where A<>''"))
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
0

You can use this code:

=IFERROR(INDEX($A$2:$A$90, MATCH(0, INDEX(COUNTIF($I$10:I10, $A$2:$A$90)+($A$2:$A$90=""), ), 0)), "")

should work

Adel Sharifat
  • 1
  • 1