2
Col A,Col B,Col C
Test1,Test1,Test3
Test2,Test1,Test3

Is there a way to go thru all cells and pick out the unique values and place them in a new column or something?

Rod
  • 14,529
  • 31
  • 118
  • 230
  • 4
    Using what (eyeballs & mouse, macros, a program)? – GreenMatt Mar 11 '11 at 16:44
  • http://stackoverflow.com/questions/4045767/extracting-unique-values-from-a-list or http://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only – Mark Baker Mar 11 '11 at 17:13

2 Answers2

1

Most of the answers on Getting unique values in Excel by using formulas only only work for values in a single column.

For a solution that works for values in multiple rows and columns, I found the following formula very useful, from http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/ Oscar at get-digital.help.com even goes through it step-by-step and with a visualized example.

1) Give the range of values the label tbl_text

2) Apply the following array formula with CTRL + SHIFT + ENTER, to cell B13 in this case. Change $B$12:B12 to refer to the cell above the cell you enter this formula into.

    =INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)

3) Copy/drag down until you get N/A's.

Community
  • 1
  • 1
Arthur Yip
  • 5,810
  • 2
  • 31
  • 50
0

If you are using Excel 2007 at least, then you can just use Remove Duplicates function from Data tab.

Otherwise I think a little bit of VBA fairy dust sprinkling is in order. I can mash up a quick VBA script if you need it.

Maverik
  • 5,619
  • 35
  • 48