Automatic filter for unique records
 

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

Automatic filter for unique records
 

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" wrote:

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

Automatic filter for unique records
 

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

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
---------------
Jacob Skaria


"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

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(h elper,ROWS($1:1))),"")
copy down





"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

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?

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

=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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

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

"Teethless mama" wrote:

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(h elper,ROWS($1:1))),"")
copy down





"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

That's excellent, thank you very much.
Martina
"Teethless mama" wrote:

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(h elper,ROWS($1:1))),"")
copy down





"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

You're Welcome!


"jc132568" wrote:


That's excellent, thank you very much.
Martina
"Teethless mama" wrote:

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(h elper,ROWS($1:1))),"")
copy down





"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

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.

--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
=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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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

Automatic filter for unique records
 

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


"Teethless mama" wrote:

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(h elper,ROWS($1:1))),"")
copy down





"jc132568" wrote:

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

"Teethless mama" wrote:

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" wrote:

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