Formulas Instead of Advanced Filter

[moily]

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.

 

[Pete_UK]

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

 

[moily]

Hi,

If I understand it correctly your suggestion would create 300 unique answers
though wouldn't it? Please tell me if I'm misunderstanding though!

Basically, I have 7 original options, 4 of those options are found in the
first column. The other columns would repeat many of the popular 4 but the 3
others can also be found in one or a few of the later columns. Example
(simplified to 5 options instead of 7 and 3 columns instead of 11):

Column1: Column 2: Column 3:
Yes No Maybe
No Yes Yes
Maybe Maybe No
No Whenever Anytime
No Maybe Whenever

I need to have a list of:
Yes
No
Maybe
Whenever
Anytime

Concatenate would create the following which would make all the rows unique
answers:
YesNoMaybe
NoYesYes
MaybeMaybeNo
NoWheneverAnytime
NoMaybeWhenever

 

[Roger Govier]

Hi Ann

If a VBA solution is acceptable to you, the following code will achieve what
you want.
I copied your data to column A:C and placed my result in column E

You would need to adjust myrange to suit your range of data and change "E"
to "B" in the code

Sub findunique()
Dim i As Long
Dim c As Range, myrange As Range
Set myrange = ActiveSheet.Range("A1:C5")
i = 15
For Each c In myrange
If WorksheetFunction.CountIf(Range(Cells(1, "E"), _
Cells(i, "E")), c.Value) = 0 Then
Cells(i, "E") = c.Value
i = i + 1
End If
Next c
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run

 

[moily]

Hi Roger,

I've used VBA in the past but I find if the spreadsheet changes
(rows/columns added/deleted) then they don't adjust accordingly as formulas
do and then they break! This is a spreadsheet that will be used for several
years at least and if I have a successor they won't know why it's broken.
It's best to use formulas unless I'm just using VBA wrong and there's a way
to have it automatically update with any changes made to the sheet?

Thank you for your suggestion though!

Best,
Ann

 

[Roger Govier]

Hi Ann

It is easy to adjust to ask the user for the range required ( which
columns).
The number of rows can be determined automatically.
Likewise, you can ask for the starting cell where the answers are to be
written.
But, if you are set against using VBA, then as you will have seen from max's
posts it is a long complicated process to deal with by formula.