Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Filter Unique Records
I would like Excel to AUTOMATICALLY give me in column B unique records from
column A A B 1 Andrew Andrew 2 Andrew Louis 3 Louis 4 Andrew Advanced filter doesn't refresh automatically. I remember a user once outlined a formula on this forum, but I can't find it now. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Filter Unique Records
Try something like this:
For a list entered into A2:A20, with A1 as the column heading Then B1: (Heading for the column....eg MyList) B2: =INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0)) Array Formula (Committed with Ctrl+Shift+Enter, instead of just Enter) B3: =IF(COUNT(MATCH(A$2:A$20,B$2:B2,0))<COUNT(1/(A$2:A$20<"")),INDEX(A$2:A$20,MATCH(0,(A$2:A$20< "")-ISNA(MATCH(A$2:A$20,B$2:B2,0)),0)),"") Copy B3 into B4 and down through as many cells as you need to cover all possible unique items With that formula, each time you enter new text that is not already listed, it will display in the list in B1:??. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Tevuna" wrote: I would like Excel to AUTOMATICALLY give me in column B unique records from column A A B 1 Andrew Andrew 2 Andrew Louis 3 Louis 4 Andrew Advanced filter doesn't refresh automatically. I remember a user once outlined a formula on this forum, but I can't find it now. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Filter Unique Records
Works great. I'm analyzing your formulas.
"Ron Coderre" wrote: Try something like this: For a list entered into A2:A20, with A1 as the column heading Then B1: (Heading for the column....eg MyList) B2: =INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0)) Array Formula (Committed with Ctrl+Shift+Enter, instead of just Enter) B3: =IF(COUNT(MATCH(A$2:A$20,B$2:B2,0))<COUNT(1/(A$2:A$20<"")),INDEX(A$2:A$20,MATCH(0,(A$2:A$20< "")-ISNA(MATCH(A$2:A$20,B$2:B2,0)),0)),"") Copy B3 into B4 and down through as many cells as you need to cover all possible unique items With that formula, each time you enter new text that is not already listed, it will display in the list in B1:??. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Tevuna" wrote: I would like Excel to AUTOMATICALLY give me in column B unique records from column A A B 1 Andrew Andrew 2 Andrew Louis 3 Louis 4 Andrew Advanced filter doesn't refresh automatically. I remember a user once outlined a formula on this forum, but I can't find it now. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Filter Unique Records
Thanks for the feedback.......
I'm glad that worked for you (I'm pretty sure that formula has Harlan Grove's fingerprints on it) *********** Regards, Ron XL2003, WinXP "Tevuna" wrote: Works great. I'm analyzing your formulas. "Ron Coderre" wrote: Try something like this: For a list entered into A2:A20, with A1 as the column heading Then B1: (Heading for the column....eg MyList) B2: =INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0)) Array Formula (Committed with Ctrl+Shift+Enter, instead of just Enter) B3: =IF(COUNT(MATCH(A$2:A$20,B$2:B2,0))<COUNT(1/(A$2:A$20<"")),INDEX(A$2:A$20,MATCH(0,(A$2:A$20< "")-ISNA(MATCH(A$2:A$20,B$2:B2,0)),0)),"") Copy B3 into B4 and down through as many cells as you need to cover all possible unique items With that formula, each time you enter new text that is not already listed, it will display in the list in B1:??. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Tevuna" wrote: I would like Excel to AUTOMATICALLY give me in column B unique records from column A A B 1 Andrew Andrew 2 Andrew Louis 3 Louis 4 Andrew Advanced filter doesn't refresh automatically. I remember a user once outlined a formula on this forum, but I can't find it now. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Filter Unique Records
Try this:
=IF(ISERR(SMALL(IF(MATCH(names,names,0)=ROW(INDIRE CT("1:"&ROWS(names))),ROW(INDIRECT("1:"&ROWS(names )))),ROWS($1:1))),"",INDEX(names,SMALL(IF(MATCH(na mes,names,0)=ROW(INDIRECT("1:"&ROWS(names))),ROW(I NDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down "Tevuna" wrote: I would like Excel to AUTOMATICALLY give me in column B unique records from column A A B 1 Andrew Andrew 2 Andrew Louis 3 Louis 4 Andrew Advanced filter doesn't refresh automatically. I remember a user once outlined a formula on this forum, but I can't find it now. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Filter Unique Records
"Teethless mama" wrote...
Try this: =IF(ISERR(SMALL(IF(MATCH(names,names,0)=ROW(INDIR ECT("1:"&ROWS(names))), ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"", INDEX(names, SMALL(IF(MATCH(names,names,0)=ROW(INDIRECT("1:"&R OWS(names))), ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) .... Or use shorter, more efficient formulas. B1: =T(A1:A100) B2 [array formula]: =IF(OR(COUNTIF(B$1:B1,A$1:A$100)=0), INDEX(A$1:A$100,MATCH(0,COUNTIF(B$1:B1,A$1:A$100), 0)),"") Fill B2 down as far as needed. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Filter Unique Records
is there a way to qualify this based on criteria? I have a list of 50,000+
records for 1100 different part numbers. some have 300 records per part, some 3. is there a way to use this logic inside an IF statement so that it would display the unique records for a specific part number within that list? "Ron Coderre" wrote: Try something like this: For a list entered into A2:A20, with A1 as the column heading Then B1: (Heading for the column....eg MyList) B2: =INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0)) Array Formula (Committed with Ctrl+Shift+Enter, instead of just Enter) B3: =IF(COUNT(MATCH(A$2:A$20,B$2:B2,0))<COUNT(1/(A$2:A$20<"")),INDEX(A$2:A$20,MATCH(0,(A$2:A$20< "")-ISNA(MATCH(A$2:A$20,B$2:B2,0)),0)),"") Copy B3 into B4 and down through as many cells as you need to cover all possible unique items With that formula, each time you enter new text that is not already listed, it will display in the list in B1:??. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Tevuna" wrote: I would like Excel to AUTOMATICALLY give me in column B unique records from column A A B 1 Andrew Andrew 2 Andrew Louis 3 Louis 4 Andrew Advanced filter doesn't refresh automatically. I remember a user once outlined a formula on this forum, but I can't find it now. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advance Filter + Unique Records | Excel Discussion (Misc queries) | |||
Filter out unique records from pivot | Excel Worksheet Functions | |||
Can you automatically filter for unique records? | Excel Worksheet Functions | |||
Filter Unique Records | Excel Discussion (Misc queries) | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) |