Automatic filter for unique records

[jc132568]

Dear experts,
I have a long list of student numbers (numbers occurring more than once). I
want to set up a formula which automatically filters for unique entries. I
used an array formula which I got on this site and it works beautifully
however it does slow down my spreadsheet. Can I filter a range A1:A500 using
database functions or a pivot table? I have read that these possibly won't
slow the system down so much.
kind regards
Martina

 

[Teethless mama]

Data > Filter > Advanced Filter > select either copy to another location or
Filter the list, in place > select your list range > Criteria range "leave it
blank" > check on unique records only

 

[jc132568]

Sorry, I want the list to be created without user intervention. Can I use
something other than an array formula as it slows excel down.
regards
Martina

 

[Jacob Skaria]

The below macro will filter the data in Col A of Sheet1 and generate the
unique list in Sheet2 Col A...If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro()

Dim lngLastRow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

lngLastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Range("A1:A" & lngLastRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("A1"), Unique:=True

End Sub



If this post helps click Yes

 

[T. Valko]

I want the list to be created without user intervention.

Post the formula you are currently using. We may be able to come up with
something more efficient.

Are you open to the idea of using functions from an add-in?

 

[jc132568]

=IF(ISERROR(MATCH(0,COUNTIF(F$3:F3,$B$3:$B$1000&""),0)),"",INDEX(IF(ISBLANK($B$3:$B$1000),"",$B$3:$B$1000),MATCH(0,COUNTIF(F$3:F3,$B$3:$B$1000&""),0)))

Yes I'm open to any suggestions.
Many thanks
Martina

 

[jc132568]

Many thanks, I'll have a go with this.
Martina

No Advanced Filter, No array formula, No problem...

Assume your data in column A with a header in row1. Defined name range
"data" no quotes

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
copy down. Defined name range in columnB "helper" of course no quotes

In C2:
=IF(COUNT(helper)>=(ROWS($1:1)),INDEX(data,SMALL(helper,ROWS($1:1))),"")
copy down

 

[jc132568]

That's excellent, thank you very much.
Martina

No Advanced Filter, No array formula, No problem...

Assume your data in column A with a header in row1. Defined name range
"data" no quotes

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
copy down. Defined name range in columnB "helper" of course no quotes

In C2:
=IF(COUNT(helper)>=(ROWS($1:1)),INDEX(data,SMALL(helper,ROWS($1:1))),"")
copy down

 

[Teethless mama]

You're Welcome!

That's excellent, thank you very much.
Martina

 

[T. Valko]

Well, I see you have replies in both posts that you are satisfied with so I
won't pursue the array formula other than this:

You can make that formula 50% faster by getting rid of the error trap! You
can use a separate formula to get the count of uniques then refer to that
formula as the error trap.

 

[jc132568]

Dear Teethless,
I have been playing with this formula for a while now just to make sure my
issue is not user related! There are cells containing zero in my data range,
and this seems to cause a problem.
a
b
0
d
e
f
g
h
0

will return the unique list

a
b
0
d
e
f
g


I won't see h until i enter something in below it, other than zero. It seems
once a zero has been encountered once, then it causes problems for data
coming immediately before the next zero, ie. the entry won't appear in the
unique list.
Can I accomodate these zeros and the effect they seem to have on the unique
filtering?

Many thanks

Martina