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

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

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

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

>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" <(E-Mail Removed)> wrote in message
news:E8DD230E-8C91-46B4-9DF9-(E-Mail Removed)...
> 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

=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" <(E-Mail Removed)> wrote in message
> news:E8DD230E-8C91-46B4-9DF9-(E-Mail Removed)...
> > 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
>
>
>

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(helper,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

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(helper,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

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(helper,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

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" <(E-Mail Removed)> wrote in message
news:B54E20CA-BD18-4145-BF6F-(E-Mail Removed)...
> =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" <(E-Mail Removed)> wrote in message
>> news:E8DD230E-8C91-46B4-9DF9-(E-Mail Removed)...
>> > 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
>>
>>
>>