E
Ellen G
I need to create a unique list and have it update automatically when new
information is added. Here's the scenario:
I have a list of locations in AJ14:AJ104. I need to extract this
information, reducing it down to unique items only. There may be blanks in
the original list that I don't want in my new list, so below is what I've
been doing. The frustration is it works in 2007 but not in 2003. I have using
exact formulas from the Excel 2003 Bible, so I can't figure out why it
doesn't work in 2003. Here goes:
AJ14:AJ104 (range name DIGIMARKETS): original list
AK14:AK104 (range name MARKETS): I use the following array formula to
return NON-BLANK cells.
=IF(ISERR(SMALL(IF(DigiMarkets<>"",ROW(INDIRECT("1:"&ROWS(DigiMarkets)))),ROW(INDIRECT("1:"&ROWS(DigiMarkets))))),"",INDEX(DigiMarkets,SMALL(IF(DigiMarkets<>"",ROW(INDIRECT("1:"&ROWS(DigiMarkets)))),ROW(INDIRECT("1:"&ROWS(DigiMarkets))))))
AL14:AL104: I then use the following array formula to return A UNIQUE LIST:
=INDEX(Markets,SMALL(IF(MATCH(Markets,Markets,0)=ROW(INDIRECT("1:"&ROWS(Markets))),MATCH(Markets,Markets,0),""),ROW(INDIRECT("1:"&ROWS(Markets)))))
FIRST PROBLEM, in 2003 the array formula in AL14:AL104 only returns the #NA
error message -- thought it works perfectly in 2007. The array formula in
AK14:AK104 works just fine.
SECOND PROBLEM, I'd like to do this is an easier way so that it doesn't take
forever to open due to all the calculations and array formulas.
Any help you could provide would be wonderful.
Thanks much,
Ellen
information is added. Here's the scenario:
I have a list of locations in AJ14:AJ104. I need to extract this
information, reducing it down to unique items only. There may be blanks in
the original list that I don't want in my new list, so below is what I've
been doing. The frustration is it works in 2007 but not in 2003. I have using
exact formulas from the Excel 2003 Bible, so I can't figure out why it
doesn't work in 2003. Here goes:
AJ14:AJ104 (range name DIGIMARKETS): original list
AK14:AK104 (range name MARKETS): I use the following array formula to
return NON-BLANK cells.
=IF(ISERR(SMALL(IF(DigiMarkets<>"",ROW(INDIRECT("1:"&ROWS(DigiMarkets)))),ROW(INDIRECT("1:"&ROWS(DigiMarkets))))),"",INDEX(DigiMarkets,SMALL(IF(DigiMarkets<>"",ROW(INDIRECT("1:"&ROWS(DigiMarkets)))),ROW(INDIRECT("1:"&ROWS(DigiMarkets))))))
AL14:AL104: I then use the following array formula to return A UNIQUE LIST:
=INDEX(Markets,SMALL(IF(MATCH(Markets,Markets,0)=ROW(INDIRECT("1:"&ROWS(Markets))),MATCH(Markets,Markets,0),""),ROW(INDIRECT("1:"&ROWS(Markets)))))
FIRST PROBLEM, in 2003 the array formula in AL14:AL104 only returns the #NA
error message -- thought it works perfectly in 2007. The array formula in
AK14:AK104 works just fine.
SECOND PROBLEM, I'd like to do this is an easier way so that it doesn't take
forever to open due to all the calculations and array formulas.
Any help you could provide would be wonderful.
Thanks much,
Ellen