Using Array Formulas in Named Ranges...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I often use the following array formula to extract unique entries from a list, but I cannot get it to work using a named range.

IF(COUNTIF($A$1:A1,A1)=1,A1," "

How would I construct this formula if the named range is in column A

Thanks

EU
 
1

It's not an array formula.

2

you don't gain anything using a named range

=IF(COUNTIF(INDEX(MyRange,1):INDEX(MyRange,ROW(1:1)),INDEX(MyRange,ROW(1:1))
)=1,INDEX(MyRange,ROW(1:1))," ")


You could use your formula and define a name for it

=MyFormula

copy down

insert>name>define

=IF(COUNTIF(Sheet1!$A$1:$A1,Sheet1!$A1)=1,Sheet1!$A1," ")


If you only want to extract unique records use the advanced filter (unique
records only)


--

Regards,

Peo Sjoblom


[email protected] said:
I often use the following array formula to extract unique entries from a
Click to expand...
list, but I cannot get it to work using a named range.
 
EU
Why do you enter your formula as an array? It should work just fine as a normal formula

If I named a range "FERD", then you could use the following formula
=IF(COUNTIF(FERD,A1)=1,A1," "

There might be two reasons why your formula doesn't work with a named range. The countif function does not work on a noncontiguous selection. If your named range includes cells that aren't grouped together the COUNTIF will return a #VALUE! error

Another possibility is becuase the range changes when you copy the formula, but when you use a named ranged it doesn't. I assume you use this formula to find the first instance of each entry. When you use a named range the COUNTIF is always looking at the full data set. Therfer the COUNTIF will only equal 1 if the item being checked is the only instance of that item. If the letter A appeares more then once in the set, then the COUNTIF will be higher then one even for the first instance of A

Hope this helps

Good Luck
Mark Graesse
(e-mail address removed)

----- (e-mail address removed) wrote: ----


I often use the following array formula to extract unique entries from a list, but I cannot get it to work using a named range.

IF(COUNTIF($A$1:A1,A1)=1,A1," "

How would I construct this formula if the named range is in column A

Thanks

EU
 
Back
Top