"Live" Advanced Filter (for unique records)

[Guest]

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks Ron. Functionally this is exactly what I'm looking for.
Unfortunately, my list that I am looking to filter contains roughly 10,000
records - thus I think I need a less complex formula (Excel crashed when I
ran the below formula on the full range).
Any ideas? I'm beginning to think that VBA is the more appropriate
solution...

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

Yikes! 10,000 records? That's the kind of information you mention at the top
of the post....not later as an "oh, by the way" : )

If you'd consider using an Advanced Filter to build the list, let me know.
You could us Adv Fltr to create the list on the same sheet as the original
list OR on another sheet. Also, automating the Adv Fltr to run on demand
using VBA is relatively easy.

***********
Regards,
Ron

XL2002, WinXP

Thanks Ron. Functionally this is exactly what I'm looking for.
Unfortunately, my list that I am looking to filter contains roughly 10,000
records - thus I think I need a less complex formula (Excel crashed when I
ran the below formula on the full range).
Any ideas? I'm beginning to think that VBA is the more appropriate
solution...

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

Haha. Sorry - I have some fairly nasty spreadsheets (financial
data-related), so I've become desensitized to large numbers I try to
avoid Index/Match-type formulae when possible, as it creates several
minute-long calculation times!

I just recorded a macro of a manual advanced filter/unique
records/filtercopy, etc. Is this the best way to go about it?

Cheers,
Russ

Yikes! 10,000 records? That's the kind of information you mention at the top
of the post....not later as an "oh, by the way" : )

If you'd consider using an Advanced Filter to build the list, let me know.
You could us Adv Fltr to create the list on the same sheet as the original
list OR on another sheet. Also, automating the Adv Fltr to run on demand
using VBA is relatively easy.

***********
Regards,
Ron

XL2002, WinXP

Thanks Ron. Functionally this is exactly what I'm looking for.
Unfortunately, my list that I am looking to filter contains roughly 10,000
records - thus I think I need a less complex formula (Excel crashed when I
ran the below formula on the full range).
Any ideas? I'm beginning to think that VBA is the more appropriate
solution...

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

I just recorded a macro of a manual advanced filter/unique

records/filtercopy, etc. Is this the best way to go about it?

Possibly.....
But, you might also try something like this:

Assumptions:
Sheet1 contains your data in cells A1:A10000 (with A1: Name)
Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: Name

Insert>Name>Define
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1

Still using Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$A$10000

(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
<Data><Filter><Advanced Filter>
Select: Copy to another location
List Range: (press F3 and select Database)
Copy To: (press F3 and select Extract)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
.....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insert>Module

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit

Sub PullUniqueData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=True
End Sub
'---Start of Code-------

To run the code:
Tools>Macro>Macros (or [Alt]+[F8])
Select and run: PullUniqueData

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Haha. Sorry - I have some fairly nasty spreadsheets (financial
data-related), so I've become desensitized to large numbers I try to
avoid Index/Match-type formulae when possible, as it creates several
minute-long calculation times!

I just recorded a macro of a manual advanced filter/unique
records/filtercopy, etc. Is this the best way to go about it?

Cheers,
Russ

Yikes! 10,000 records? That's the kind of information you mention at the top
of the post....not later as an "oh, by the way" : )

If you'd consider using an Advanced Filter to build the list, let me know.
You could us Adv Fltr to create the list on the same sheet as the original
list OR on another sheet. Also, automating the Adv Fltr to run on demand
using VBA is relatively easy.

***********
Regards,
Ron

XL2002, WinXP

Thanks Ron. Functionally this is exactly what I'm looking for.
Unfortunately, my list that I am looking to filter contains roughly 10,000
records - thus I think I need a less complex formula (Excel crashed when I
ran the below formula on the full range).
Any ideas? I'm beginning to think that VBA is the more appropriate
solution...

:

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

Yes, I can work with that. Thanks v much for your insights.

I just recorded a macro of a manual advanced filter/unique
records/filtercopy, etc. Is this the best way to go about it?

Possibly.....
But, you might also try something like this:

Assumptions:
Sheet1 contains your data in cells A1:A10000 (with A1: Name)
Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: Name

Insert>Name>Define
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1

Still using Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$A$10000

(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
<Data><Filter><Advanced Filter>
Select: Copy to another location
List Range: (press F3 and select Database)
Copy To: (press F3 and select Extract)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insert>Module

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit

Sub PullUniqueData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=True
End Sub
'---Start of Code-------

To run the code:
Tools>Macro>Macros (or [Alt]+[F8])
Select and run: PullUniqueData

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Haha. Sorry - I have some fairly nasty spreadsheets (financial
data-related), so I've become desensitized to large numbers I try to
avoid Index/Match-type formulae when possible, as it creates several
minute-long calculation times!

I just recorded a macro of a manual advanced filter/unique
records/filtercopy, etc. Is this the best way to go about it?

Cheers,
Russ

Yikes! 10,000 records? That's the kind of information you mention at the top
of the post....not later as an "oh, by the way" : )

If you'd consider using an Advanced Filter to build the list, let me know.
You could us Adv Fltr to create the list on the same sheet as the original
list OR on another sheet. Also, automating the Adv Fltr to run on demand
using VBA is relatively easy.

***********
Regards,
Ron

XL2002, WinXP


:

Thanks Ron. Functionally this is exactly what I'm looking for.
Unfortunately, my list that I am looking to filter contains roughly 10,000
records - thus I think I need a less complex formula (Excel crashed when I
ran the below formula on the full range).
Any ideas? I'm beginning to think that VBA is the more appropriate
solution...

:

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

I'm glad I could help.....and thanks for the feedback.


***********
Regards,
Ron

XL2002, WinXP

Yes, I can work with that. Thanks v much for your insights.

I just recorded a macro of a manual advanced filter/unique
records/filtercopy, etc. Is this the best way to go about it?

Possibly.....
But, you might also try something like this:

Assumptions:
Sheet1 contains your data in cells A1:A10000 (with A1: Name)
Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: Name

Insert>Name>Define
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1

Still using Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$A$10000

(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
<Data><Filter><Advanced Filter>
Select: Copy to another location
List Range: (press F3 and select Database)
Copy To: (press F3 and select Extract)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insert>Module

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit

Sub PullUniqueData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=True
End Sub
'---Start of Code-------

To run the code:
Tools>Macro>Macros (or [Alt]+[F8])
Select and run: PullUniqueData

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Haha. Sorry - I have some fairly nasty spreadsheets (financial
data-related), so I've become desensitized to large numbers I try to
avoid Index/Match-type formulae when possible, as it creates several
minute-long calculation times!

I just recorded a macro of a manual advanced filter/unique
records/filtercopy, etc. Is this the best way to go about it?

Cheers,
Russ



:

Yikes! 10,000 records? That's the kind of information you mention at the top
of the post....not later as an "oh, by the way" : )

If you'd consider using an Advanced Filter to build the list, let me know.
You could us Adv Fltr to create the list on the same sheet as the original
list OR on another sheet. Also, automating the Adv Fltr to run on demand
using VBA is relatively easy.

***********
Regards,
Ron

XL2002, WinXP


:

Thanks Ron. Functionally this is exactly what I'm looking for.
Unfortunately, my list that I am looking to filter contains roughly 10,000
records - thus I think I need a less complex formula (Excel crashed when I
ran the below formula on the full range).
Any ideas? I'm beginning to think that VBA is the more appropriate
solution...

:

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

This is a formaula that I need. Every time I follow your instructions it
comes back #N/A.
Can you give me step by step instructions, I know its got to be something
I'm missing.

What am I doing wrong.

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

How about this, instead.....A working example.

Go to the Data Validation section of this website:
http://www.contextures.com/excelfilesRon.html

and download this file:
DataValFlexList.zip

Does that help?
(Post back if you have more questions)
***********
Regards,
Ron

XL2003, WinXP

This is a formaula that I need. Every time I follow your instructions it
comes back #N/A.
Can you give me step by step instructions, I know its got to be something
I'm missing.

What am I doing wrong.

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

Ahhh....The example is exactly what your post needed!

That is an ARRAY FORMULA.

Edit the cell B2....don't make any changes...
Then.....Hold down [Ctrl] [Shift] and press [Enter]
(instead of just pressing [Enter]

Next ....
Copy B2 and paste into B3 and down as far as you need.

All set now?

***********
Regards,
Ron

XL2003, WinXP

A1=CSR
B1=UniqueNames

=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

I get this error.

CSR UniqueNames
Test-5 #N/A
Test-5 #N/A
Test-10 #N/A
Test-10
Test-11

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

A1=CSR
B1=UniqueNames

=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

I get this error.

CSR UniqueNames
Test-5 #N/A
Test-5 #N/A
Test-10 #N/A
Test-10
Test-11

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

That's the step I miised. I know I was skipping sometihng. Thanx.

Ahhh....The example is exactly what your post needed!

That is an ARRAY FORMULA.

Edit the cell B2....don't make any changes...
Then.....Hold down [Ctrl] [Shift] and press [Enter]
(instead of just pressing [Enter]

Next ....
Copy B2 and paste into B3 and down as far as you need.

All set now?

***********
Regards,
Ron

XL2003, WinXP

A1=CSR
B1=UniqueNames

=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

I get this error.

CSR UniqueNames
Test-5 #N/A
Test-5 #N/A
Test-10 #N/A
Test-10
Test-11

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

Ok now I'm getting:

MGR Manager
Rossyion, Nellie Rossyion, Nellie
Williams, Jason Rossyion, Nellie
Ledet, Karen Rossyion, Nellie
Ledet, Pixie Rossyion, Nellie
Dugar, Jerard Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie
Rossyion, Nellie Rossyion, Nellie
Schopf, James Rossyion, Nellie
Simien, Amelia Rossyion, Nellie
Wiltz, Robert Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie
Lejeune, Jessica Rossyion, Nellie
Lejeune, Jessica Rossyion, Nellie
Malacaman, Angie Rossyion, Nellie
Malacaman, Angie Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie

A1==OFFSET('Non Resolution-Priority Calls'!$C$1,0,0,COUNTA('Non
Resolution-Priority Calls'!$C$1:$C$179),1)

Ahhh....The example is exactly what your post needed!

That is an ARRAY FORMULA.

Edit the cell B2....don't make any changes...
Then.....Hold down [Ctrl] [Shift] and press [Enter]
(instead of just pressing [Enter]

Next ....
Copy B2 and paste into B3 and down as far as you need.

All set now?

***********
Regards,
Ron

XL2003, WinXP

A1=CSR
B1=UniqueNames

=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

I get this error.

CSR UniqueNames
Test-5 #N/A
Test-5 #N/A
Test-10 #N/A
Test-10
Test-11

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[T. Valko]

Here's a sample file based on your posted sample data:

Extract unique TEXT 22.5kb

http://cjoint.com/?idfnpvrJyP

I set of formulas use a static range and another set of formulas use a
dynamic range. The dynamic range is set to row 200.

Both formulas are array formulas. Array formulas need to be entered using
the key combination of CTRL,SHIFT,ENTER (not just ENTER). Also, if you ever
change or edit an array formula it must be re-entered as an array using the
key combo.

--
Biff
Microsoft Excel MVP

Ok now I'm getting:

MGR Manager
Rossyion, Nellie Rossyion, Nellie
Williams, Jason Rossyion, Nellie
Ledet, Karen Rossyion, Nellie
Ledet, Pixie Rossyion, Nellie
Dugar, Jerard Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie
Rossyion, Nellie Rossyion, Nellie
Schopf, James Rossyion, Nellie
Simien, Amelia Rossyion, Nellie
Wiltz, Robert Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie
Lejeune, Jessica Rossyion, Nellie
Lejeune, Jessica Rossyion, Nellie
Malacaman, Angie Rossyion, Nellie
Malacaman, Angie Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie

A1==OFFSET('Non Resolution-Priority Calls'!$C$1,0,0,COUNTA('Non
Resolution-Priority Calls'!$C$1:$C$179),1)

Ahhh....The example is exactly what your post needed!

That is an ARRAY FORMULA.

Edit the cell B2....don't make any changes...
Then.....Hold down [Ctrl] [Shift] and press [Enter]
(instead of just pressing [Enter]

Next ....
Copy B2 and paste into B3 and down as far as you need.

All set now?

***********
Regards,
Ron

XL2003, WinXP

A1=CSR
B1=UniqueNames

=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

I get this error.

CSR UniqueNames
Test-5 #N/A
Test-5 #N/A
Test-10 #N/A
Test-10
Test-11


















:

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are
encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

Is there a function, or other method, to maintain a real-time list
of unique
records. In other words, I'd like to NOT have to manually run
Advanced
Filter/Paste every time a new unique record is added. I assume I
could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!

 

[Guest]

If you used the array formula you posted, then you should be getting the
correct list of unique items.

Using your posted list.....
B1: UniqueNames
B2:
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Copy B2 into B3 and down as far as you need.

With that formula, these are my results:
UniqueNames
Rossyion, Nellie Rossyion, Nellie
Williams, Jason Rossyion, Nellie
Ledet, Karen Rossyion, Nellie
Ledet, Pixie Rossyion, Nellie
Dugar, Jerard Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie
Schopf, James Rossyion, Nellie
Simien, Amelia Rossyion, Nellie
Wiltz, Robert Rossyion, Nellie
Lejeune, Jessica Rossyion, Nellie
Malacaman, Angie Rossyion, Nellie

If the number of items in the list will vary, you might consider basing the
formula on a Dynamic Range Name. See Debra Dalgleish's website for
instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?
***********
Regards,
Ron

XL2003, WinXP

Ok now I'm getting:

MGR Manager
Rossyion, Nellie Rossyion, Nellie
Williams, Jason Rossyion, Nellie
Ledet, Karen Rossyion, Nellie
Ledet, Pixie Rossyion, Nellie
Dugar, Jerard Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie
Rossyion, Nellie Rossyion, Nellie
Schopf, James Rossyion, Nellie
Simien, Amelia Rossyion, Nellie
Wiltz, Robert Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie
Lejeune, Jessica Rossyion, Nellie
Lejeune, Jessica Rossyion, Nellie
Malacaman, Angie Rossyion, Nellie
Malacaman, Angie Rossyion, Nellie
Stimpson, Deborah Rossyion, Nellie

A1==OFFSET('Non Resolution-Priority Calls'!$C$1,0,0,COUNTA('Non
Resolution-Priority Calls'!$C$1:$C$179),1)

Ahhh....The example is exactly what your post needed!

That is an ARRAY FORMULA.

Edit the cell B2....don't make any changes...
Then.....Hold down [Ctrl] [Shift] and press [Enter]
(instead of just pressing [Enter]

Next ....
Copy B2 and paste into B3 and down as far as you need.

All set now?

***********
Regards,
Ron

XL2003, WinXP

A1=CSR
B1=UniqueNames

=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

I get this error.

CSR UniqueNames
Test-5 #N/A
Test-5 #N/A
Test-10 #N/A
Test-10
Test-11


















:

With
A1 contains the title of the list (eg: Names)
A2:A20 containing a contiguous list
(which may include duplicates. blanks only at the end)

Try something like this:

B1: UniqueNames
Enter this ARRAY FORMULA in B2
=IF(SUMPRODUCT(($A$2:$A$20<>"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B1)-1,"",INDEX($A$2:$A$20,MATCH(TRUE,ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B2 and paste into B3 and down as far as you need

Example:
If A1:A8 contains
Title
Company_02
Company_02
Company_03
Company_01
Company_02
Company_03
Company_03

The formulas return these values in B1:B4
Title
Company_02
Company_03
Company_01

NOTE: the unique values are listed in the order in which they are encountered

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

Is there a function, or other method, to maintain a real-time list of unique
records. In other words, I'd like to NOT have to manually run Advanced
Filter/Paste every time a new unique record is added. I assume I could do
this with a macro, but am trying to keep the file macro-free. Any
suggestions?
Thanks as always!