Extract unique values but ignoring errors

**LadyS** · 09-04-2014, 10:58 AM

Hi,

I'm sure this is an easy fix but I can't seem to find the answer to this. I am using INDEX, MATCH, and COUNTIF to extract a list of unique values from a single column, however, the column also includes #N/A errors (they also contain formulas). What's a good formula to use to ignore extracting the errors as a unique value? Here's the formula I am using. My values are in Column E and my list is in Column AA.

={INDEX($E$3:$E$500, MATCH(0, COUNTIF(AA2:$AA$2, $E$3:$E$500), 0))}

Any help would be great

!

**mikeTRON** · 09-04-2014, 11:23 AM

Why use a formula? Why not use a pivot table and then remove the error?

**LadyS** · 09-04-2014, 11:34 AM

Thanks for the reply,

I'm actually using it to make a conditional scatter plot which I can't do with pivot tables.

**AlKey** · 09-04-2014, 12:08 PM

Try this array formula. It will ignore errors.

=IFERROR(INDEX($E$3:$E$500,MATCH(0,IF(ISERROR($E$3:$E$500),"",COUNTIF($AA$2:AA2, $E$3:$E$500)), 0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**mikeTRON** · 09-04-2014, 12:10 PM

Originally Posted by LadyS

Thanks for the reply,

I'm actually using it to make a conditional scatter plot which I can't do with pivot tables.

Are you not using Excel to process the data or format it before getting it ready for the scatter plot data layout? You can use pivots for that just as easily as a slick array formula.

**mikeTRON** · 09-04-2014, 12:13 PM

EDIT: Whoops, double post.

**LadyS** · 09-04-2014, 12:21 PM

AlKey,

Works perfectly!! Thank you !!

Extract unique values but ignoring errors

LinkBack

Thread Tools

Rate This Thread

Display

Extract unique values but ignoring errors

Re: Extract unique values but ignoring errors

Re: Extract unique values but ignoring errors

Re: Extract unique values but ignoring errors

Re: Extract unique values but ignoring errors

Re: Extract unique values but ignoring errors

Re: Extract unique values but ignoring errors

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find unique values from range ( populated cells only) & not count zeros or NA errors VBA

[SOLVED] Compare two ranges of data and give unique values in two different columns ignoring blanks

[SOLVED] Extract Unique Values From Column In One Sheet and Populate Unique Values In Another Sheet

Extract Unique Values, Then Extract Again to Remove Suffixes

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions