Excel newbie stymied

urbanvole

I am trying to create a list of unique values from an entire column in another sheet. To do so I am using the following function:

=IFERROR(INDEX(Export!$M$2:$M$60000, MATCH(0, IF(ISBLANK(Export!$M$2:$M$60000), 1, COUNTIF($A$1:A1, Export!$M$2:$M$60000)), 0)), "")

When activated, however, the function returns the first value over and over. I'm not sure what I am doing incorrectly - although I'm sure it is something fairly basic and obvious - and would appreciate any assistance anyone might be able to offer!

Thanks in advance,
uv

urbanvole

NM - It randomly works now. Don't know why, but I'll take it! Onwards!

Cauld

why not copy and paste the list and then do "remove duplicates"?

Pailryder

or safer would be to add a column that shows if a value is a duplicate or not by counting a specific value and if it sees only one then you can build off a list of those safe values.

Serpent

Or you could pivot it and put that column in the 'rows' field of the pivot. Or you could use the advanced filter and filter to unique values. Or you could use Power Query and remove duplicates. There's about 1000 ways to do this and most of them are easier than your formula.

However, the fact you could build up that formula itself implies you're not an excel newbie. Give yourself some credit! Index/Match itself is 'advanced' as it shows you've moved beyond vlookup...

useless4

Yea dump your data into a table, make a pivot off of it and remind everyone to hit refresh data every time they update the table.

Pailryder

hey now, never underestimate the power of vlookup!