I have a table of values that span across a range of rows and columns. Example data:

Green    Leaf
Green    Fire
Red      Fire
Water    Blue

I would like a single column of unique values from the table. Result:


I would prefer to use only formulas if possible. I have tried using the Advanced Filter Tool in the Data ribbon menu shown here, but it results in two columns instead of one.

  • Must it be formulas? Could it be VBA?. If it has to be formulas, can you use helper columns? Also: can a given value appear in both columns – cybernetic.nomad Oct 10 '18 at 18:33
  • 1
    You can't break the data down to one column? If you can do that then all you need to do is attach an assist column that puts an index for those that are unique and then collect the values with an index in the final column – Eric F Oct 10 '18 at 18:36
  • @cybernetic.nomad I can use helper columns. A given value can appear in both columns, but not on the same row. – EarthIsHome Oct 10 '18 at 18:37
  • @EricF No can do; this data is part of a larger table with more data on the same row. (i.e. green and leaf correspond to more data on the same row.) – EarthIsHome Oct 10 '18 at 18:39
  • @EricF Is there a way of breaking the columns down into one using formulas? – EarthIsHome Oct 10 '18 at 18:43

you can use this formula:


It iterates through the first column of values till it errors, then iterates the second as it is dragged down.

The important thing to remember is that it needs to be in at least the second row and the $D$1:D1 should refer to the cell directly above the cell in which the formula is first placed, paying attention to what is and what is not absolute in the reference.

enter image description here

  • If my table starts on the second row, does that mean the formula must start on the third row? – EarthIsHome Oct 10 '18 at 18:53
  • no, only needs to have a cell above to which it can refer. – Scott Craner Oct 10 '18 at 18:54
  • @ScottCraner I like this answer better than the one I was thinking of. Very clean so +1 from me – Eric F Oct 10 '18 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.