Formulas Instead of Advanced Filter

Hi there,

I have Excel 2003 and want to have formulas that do the same work as
Advanced Filtering. I currently have a system that looks at data in one
column (X) and, with a mixture of If, Index and Match formulas, determines
unique data and places it in a neat column (AI). The formulas are below.
However, I now have eleven columns that hold most of the same data but each
column may (or may not) have one or two more unique data in addition to the
data that is shared by the majority. If I had the data in columns V2:AF300
and wanted to have the unique information in a neat column starting from B15
how could I ammend the below formulas or what could I use instead?

Thanks in advance for your help!
Ann

I currently have data in X2:X495
I then have the following in cell AI2:
=IF(INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0))=".","EXTRA",INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0)))
And in cell AI3:
=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)),""))
And then drag down the formulas from cell AI3.

Perhaps you could use a helper column (AG ?) and concatenate the data
from those 11 columns:

=V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2&AE2&AF2

copied down. Then instead of column X in your formulae you could
change this to column AG.

Hope this helps.

Pete

On Jan 19, 4:12 pm, moily wrote:
> Hi there,
>
> I have Excel 2003 and want to have formulas that do the same work as
> Advanced Filtering.  I currently have a system that looks at data in one
> column (X) and, with a mixture of If, Index and Match formulas, determines
> unique data and places it in a neat column (AI).  The formulas are below.  
> However, I now have eleven columns that hold most of the same data but each
> column may (or may not) have one or two more unique data in addition to the
> data that is shared by the majority.  If I had the data in columns V2:AF300
> and wanted to have the unique information in a neat column starting from B15
> how could I ammend the below formulas or what could I use instead?
>
> Thanks in advance for your help!
> Ann
>
> I currently have data in X2:X495
> I then have the following in cell AI2:
> =IF(INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0))=".","EXTRA",INDEX(X2:X495,M­ATCH(0,-ISBLANK(X2:X495),0)))
> And in cell AI3:
> =IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$­2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)),"")="­.","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),I­NDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)­),""))
> And then drag down the formulas from cell AI3.