Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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:$B 1)-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 "RussG" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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... "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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 "RussG" wrote: 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... "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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 "Ron Coderre" wrote: 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 "RussG" wrote: 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... "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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 InsertNameDefine Names in workbook: Sheet2!Extract Refers to: =Sheet2!$A$1 Still using Sheet2: InsertNameDefine 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: InsertModule 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: ToolsMacroMacros (or [Alt]+[F8]) Select and run: PullUniqueData Is that something you can work with? *********** Regards, Ron XL2002, WinXP "RussG" wrote: 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 "Ron Coderre" wrote: 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 "RussG" wrote: 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... "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
Yes, I can work with that. Thanks v much for your insights.
"Ron Coderre" wrote: 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 InsertNameDefine Names in workbook: Sheet2!Extract Refers to: =Sheet2!$A$1 Still using Sheet2: InsertNameDefine 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: InsertModule 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: ToolsMacroMacros (or [Alt]+[F8]) Select and run: PullUniqueData Is that something you can work with? *********** Regards, Ron XL2002, WinXP "RussG" wrote: 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 "Ron Coderre" wrote: 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 "RussG" wrote: 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... "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
I'm glad I could help.....and thanks for the feedback.
*********** Regards, Ron XL2002, WinXP "RussG" wrote: Yes, I can work with that. Thanks v much for your insights. "Ron Coderre" wrote: 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 InsertNameDefine Names in workbook: Sheet2!Extract Refers to: =Sheet2!$A$1 Still using Sheet2: InsertNameDefine 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: InsertModule 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: ToolsMacroMacros (or [Alt]+[F8]) Select and run: PullUniqueData Is that something you can work with? *********** Regards, Ron XL2002, WinXP "RussG" wrote: 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 "Ron Coderre" wrote: 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 "RussG" wrote: 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... "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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. "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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 "David A." wrote: 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. "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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 "David A." wrote: A1=CSR B1=UniqueNames =IF(SUMPRODUCT(($A$2:$A$20<"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B 1)-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 "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
A1=CSR
B1=UniqueNames =IF(SUMPRODUCT(($A$2:$A$20<"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B 1)-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 "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
That's the step I miised. I know I was skipping sometihng. Thanx.
"Ron Coderre" wrote: 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 "David A." wrote: A1=CSR B1=UniqueNames =IF(SUMPRODUCT(($A$2:$A$20<"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B 1)-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 "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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) "Ron Coderre" wrote: 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 "David A." wrote: A1=CSR B1=UniqueNames =IF(SUMPRODUCT(($A$2:$A$20<"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B 1)-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 "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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 "David A." wrote in message ... 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) "Ron Coderre" wrote: 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 "David A." wrote: A1=CSR B1=UniqueNames =IF(SUMPRODUCT(($A$2:$A$20<"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B 1)-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 "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Live" Advanced Filter (for unique records)
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:$B 1)-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 "David A." wrote: 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) "Ron Coderre" wrote: 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 "David A." wrote: A1=CSR B1=UniqueNames =IF(SUMPRODUCT(($A$2:$A$20<"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B 1)-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 "Ron Coderre" wrote: 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:$B 1)-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 "RussG" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a copy of a chart remain "live" | Charts and Charting in Excel | |||
Advanced filter formula for "contains="Sept" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Error using Advanced Filter Unique Records | Excel Discussion (Misc queries) | |||
Inconsistent Results: Advanced Filter Unique Records Only | Excel Discussion (Misc queries) |