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(Dig iMarkets))))),"",INDEX(DigiMarkets,SMALL(IF(DigiMa rkets<"",ROW(INDIRECT("1:"&ROWS(DigiMarkets)))),R OW(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)=R OW(INDIRECT("1:"&ROWS(Markets))),MATCH(Markets,Mar kets,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

Ellen,

Try this.

http://www.ozgrid.com/forum/showthread.php?t=64237

It uses VBA code to extract unique values.

HTH

"Ellen G" wrote:

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(Dig iMarkets))))),"",INDEX(DigiMarkets,SMALL(IF(DigiMa rkets<"",ROW(INDIRECT("1:"&ROWS(DigiMarkets)))),R OW(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)=R OW(INDIRECT("1:"&ROWS(Markets))),MATCH(Markets,Mar kets,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

Thanks so much -- however, it appears that the VBA code requires user input
or manipulation -- unless I'm misunderstanding it.

I am building a "template" that multiple end users will use. I have
automated a large part of it, but am hesitant to require them to doing
something they easily forget that would impact the outcome.

My frustration is that the arrays I use are completely successful in 2007
and doing exactly what I need them to do. However, it is not successful in
2003, which my of my end users will be using.

Sigh.

Ellen

"Huber57" wrote:

Ellen,

Try this.

http://www.ozgrid.com/forum/showthread.php?t=64237

It uses VBA code to extract unique values.

HTH

"Ellen G" wrote:

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(Dig iMarkets))))),"",INDEX(DigiMarkets,SMALL(IF(DigiMa rkets<"",ROW(INDIRECT("1:"&ROWS(DigiMarkets)))),R OW(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)=R OW(INDIRECT("1:"&ROWS(Markets))),MATCH(Markets,Mar kets,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

Ellen,

Another way to do it would be to record a macro as you do an advanced filter
and paste unique values in a new location. It would still capture the blank
cell (if there were at least one), but then you could sort the new list to
put the blank at the bottom.



"Ellen G" wrote:

Thanks so much -- however, it appears that the VBA code requires user input
or manipulation -- unless I'm misunderstanding it.

I am building a "template" that multiple end users will use. I have
automated a large part of it, but am hesitant to require them to doing
something they easily forget that would impact the outcome.

My frustration is that the arrays I use are completely successful in 2007
and doing exactly what I need them to do. However, it is not successful in
2003, which my of my end users will be using.

Sigh.

Ellen

"Huber57" wrote:

Ellen,

Try this.

http://www.ozgrid.com/forum/showthread.php?t=64237

It uses VBA code to extract unique values.

HTH

"Ellen G" wrote:

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(Dig iMarkets))))),"",INDEX(DigiMarkets,SMALL(IF(DigiMa rkets<"",ROW(INDIRECT("1:"&ROWS(DigiMarkets)))),R OW(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)=R OW(INDIRECT("1:"&ROWS(Markets))),MATCH(Markets,Mar kets,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

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, ...

Here's a different approach that might help getting started.

Start with columns AK and AL empty.

In AK14, put
=IF(COUNTIF($AJ$14:$AJ14,$AJ14)=1,MAX($AK$13:$AK13 )+1,"")

In AL14, put
=IF(ROW()-13MAX(AK:AK),"",
OFFSET($AJ$14,MATCH(ROW()-13,AK:AK,0)-14,0))

Then select AK14:AL14 and copy down to row 104.

Modify to suit.

Hello Ellen,

I suggest to look at
http://sulprobil.com/html/listfreq.html

It's really your choice:
1. Unique filter
2. Pivot table
3. VBA
4. Worksheet functions

Regards,
Bernd