| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Making list of unique items from two different columns
I have several unique formulas I am using for various purposes but none can
solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
I think I understand, but not totally sure. Anyway, try this an post back if
it doesn't work... ColumnA ABC DEF ABC ABC MNB ABC ColumnB 123A 16LM 437F 123A 789H 437F In E1 put ABC In F1 paste this function: =IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"") When you enter it, hit Ctrl + Shift + Enter (it is an array). Watch the magic show!! Regards, Ryan--- -- RyGuy "casey" wrote: I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
Very nice! A formula worth keeping.
-- Gary''s Student - gsnu2007xx "ryguy7272" wrote: I think I understand, but not totally sure. Anyway, try this an post back if it doesn't work... ColumnA ABC DEF ABC ABC MNB ABC ColumnB 123A 16LM 437F 123A 789H 437F In E1 put ABC In F1 paste this function: =IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"") When you enter it, hit Ctrl + Shift + Enter (it is an array). Watch the magic show!! Regards, Ryan--- -- RyGuy "casey" wrote: I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
Ryan,
Thnx for such a quick reply. This works almost to a "T", except that it lists all of the entries in Col B including dupes, ie: ABC 123A 123A 437F 437F The return I need is: ABC 123A 437F Thnx again, casey .. Can this one be tweaked to only show the unique entries? "ryguy7272" wrote: I think I understand, but not totally sure. Anyway, try this an post back if it doesn't work... ColumnA ABC DEF ABC ABC MNB ABC ColumnB 123A 16LM 437F 123A 789H 437F In E1 put ABC In F1 paste this function: =IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"") When you enter it, hit Ctrl + Shift + Enter (it is an array). Watch the magic show!! Regards, Ryan--- -- RyGuy "casey" wrote: I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
Try this:
In D1: =IF(ISERR(SMALL(IF(FREQUENCY(IF($A$1:$A$6=C$1,MATC H($B$1:$B$6,$B$1:$B$6,0)),MATCH($B$1:$B$6,$B$1:$B$ 6,0))0,ROW(INDIRECT("1:"&ROWS($A$1:$A$6)))),ROWS( $1:1))),"",INDEX($B$1:$B$6,SMALL(IF(FREQUENCY(IF($ A$1:$A$6=C$1,MATCH($B$1:$B$6,$B$1:$B$6,0)),MATCH($ B$1:$B$6,$B$1:$B$6,0))0,ROW(INDIRECT("1:"&ROWS($A $1:$A$6)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down "casey" wrote: I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
Try this:
A1:An = rng1 B1:Bn = rng2 C1 = ABC Enter this array formula** in C2. This will return the count of items that meet the criteria: =COUNT(1/FREQUENCY(IF((rng1=C$1)*(rng2<""),MATCH(rng1&rng2 ,rng1&rng2,0)),ROW(rng2)-MIN(ROW(rng2))+1)) Enter this array formula** in D1 and copy down until you get blanks. You need to copy to a number of cells that is *at least equal to the number returned in cell C2*: =IF(ROWS(D$1:D1)<=C$2,INDEX(rng2,SMALL(IF((rng1=C$ 1)*(rng2<""),IF(MATCH(rng1&rng2,rng1&rng2,0)=ROW( rng2)-MIN(ROW(rng2))+1,ROW(rng2)-MIN(ROW(rng2))+1)),ROWS(D$1:D1))),"") These formulas account for empty cells. If there are no empty cells in your ranges then we can simplify the formulas and save some resources! Also, if your ranges are in set locations and you will *never* insert new rows above the range we can simplify the formulas to save some resources! ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "casey" wrote in message ... I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
Biff,
Thanks for your reply. I got the first formula to work fine. The second only gives me #NUM! I've tried several changes to no avail. To answer your questions...The only empty cells are rows on the bottom of the defined arrays rng1 is actually A2:A3000 and rng2 is C2:C3000. (I have an intermediate column.) Also, there will never be any new rows above the range. Thanks, casey "T. Valko" wrote: Try this: A1:An = rng1 B1:Bn = rng2 C1 = ABC Enter this array formula** in C2. This will return the count of items that meet the criteria: =COUNT(1/FREQUENCY(IF((rng1=C$1)*(rng2<""),MATCH(rng1&rng2 ,rng1&rng2,0)),ROW(rng2)-MIN(ROW(rng2))+1)) Enter this array formula** in D1 and copy down until you get blanks. You need to copy to a number of cells that is *at least equal to the number returned in cell C2*: =IF(ROWS(D$1:D1)<=C$2,INDEX(rng2,SMALL(IF((rng1=C$ 1)*(rng2<""),IF(MATCH(rng1&rng2,rng1&rng2,0)=ROW( rng2)-MIN(ROW(rng2))+1,ROW(rng2)-MIN(ROW(rng2))+1)),ROWS(D$1:D1))),"") These formulas account for empty cells. If there are no empty cells in your ranges then we can simplify the formulas and save some resources! Also, if your ranges are in set locations and you will *never* insert new rows above the range we can simplify the formulas to save some resources! ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "casey" wrote in message ... I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
Here's a small sample file that demonstrates this.
xExtractUniques.xls 16kb http://www.freefilehosting.net/download/3ee53 Sheet1 uses the same formulas I suggested in my other reply. Sheet2 uses formulas *based on the conditions* that there are no empty cells *within* the range and the data will *always* start on row 2 and rows will *never* be inserted above the range. These restrictive conditions allow us to use shorter more efficient formulas. The formulas on Sheet2 also use dynamic ranges so it's assumed the data will *always* be a contiguous range. -- Biff Microsoft Excel MVP "casey" wrote in message ... Biff, Thanks for your reply. I got the first formula to work fine. The second only gives me #NUM! I've tried several changes to no avail. To answer your questions...The only empty cells are rows on the bottom of the defined arrays rng1 is actually A2:A3000 and rng2 is C2:C3000. (I have an intermediate column.) Also, there will never be any new rows above the range. Thanks, casey "T. Valko" wrote: Try this: A1:An = rng1 B1:Bn = rng2 C1 = ABC Enter this array formula** in C2. This will return the count of items that meet the criteria: =COUNT(1/FREQUENCY(IF((rng1=C$1)*(rng2<""),MATCH(rng1&rng2 ,rng1&rng2,0)),ROW(rng2)-MIN(ROW(rng2))+1)) Enter this array formula** in D1 and copy down until you get blanks. You need to copy to a number of cells that is *at least equal to the number returned in cell C2*: =IF(ROWS(D$1:D1)<=C$2,INDEX(rng2,SMALL(IF((rng1=C$ 1)*(rng2<""),IF(MATCH(rng1&rng2,rng1&rng2,0)=ROW( rng2)-MIN(ROW(rng2))+1,ROW(rng2)-MIN(ROW(rng2))+1)),ROWS(D$1:D1))),"") These formulas account for empty cells. If there are no empty cells in your ranges then we can simplify the formulas and save some resources! Also, if your ranges are in set locations and you will *never* insert new rows above the range we can simplify the formulas to save some resources! ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "casey" wrote in message ... I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
Your formula worked perfectly! I figured out what I had done wrong. I guess
I had to sleep on it. :-) Thanks, casey "T. Valko" wrote: Here's a small sample file that demonstrates this. xExtractUniques.xls 16kb http://www.freefilehosting.net/download/3ee53 Sheet1 uses the same formulas I suggested in my other reply. Sheet2 uses formulas *based on the conditions* that there are no empty cells *within* the range and the data will *always* start on row 2 and rows will *never* be inserted above the range. These restrictive conditions allow us to use shorter more efficient formulas. The formulas on Sheet2 also use dynamic ranges so it's assumed the data will *always* be a contiguous range. -- Biff Microsoft Excel MVP "casey" wrote in message ... Biff, Thanks for your reply. I got the first formula to work fine. The second only gives me #NUM! I've tried several changes to no avail. To answer your questions...The only empty cells are rows on the bottom of the defined arrays rng1 is actually A2:A3000 and rng2 is C2:C3000. (I have an intermediate column.) Also, there will never be any new rows above the range. Thanks, casey "T. Valko" wrote: Try this: A1:An = rng1 B1:Bn = rng2 C1 = ABC Enter this array formula** in C2. This will return the count of items that meet the criteria: =COUNT(1/FREQUENCY(IF((rng1=C$1)*(rng2<""),MATCH(rng1&rng2 ,rng1&rng2,0)),ROW(rng2)-MIN(ROW(rng2))+1)) Enter this array formula** in D1 and copy down until you get blanks. You need to copy to a number of cells that is *at least equal to the number returned in cell C2*: =IF(ROWS(D$1:D1)<=C$2,INDEX(rng2,SMALL(IF((rng1=C$ 1)*(rng2<""),IF(MATCH(rng1&rng2,rng1&rng2,0)=ROW( rng2)-MIN(ROW(rng2))+1,ROW(rng2)-MIN(ROW(rng2))+1)),ROWS(D$1:D1))),"") These formulas account for empty cells. If there are no empty cells in your ranges then we can simplify the formulas and save some resources! Also, if your ranges are in set locations and you will *never* insert new rows above the range we can simplify the formulas to save some resources! ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "casey" wrote in message ... I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
Thanks to you, too, "Teethless mama" for your response. I had never used the
ISERR in a formula and was apprehensive to do so. Now that I have the formula working from Biff, I'll try yours. I DO appreciate your reply to my question. casey "Teethless mama" wrote: Try this: In D1: =IF(ISERR(SMALL(IF(FREQUENCY(IF($A$1:$A$6=C$1,MATC H($B$1:$B$6,$B$1:$B$6,0)),MATCH($B$1:$B$6,$B$1:$B$ 6,0))0,ROW(INDIRECT("1:"&ROWS($A$1:$A$6)))),ROWS( $1:1))),"",INDEX($B$1:$B$6,SMALL(IF(FREQUENCY(IF($ A$1:$A$6=C$1,MATCH($B$1:$B$6,$B$1:$B$6,0)),MATCH($ B$1:$B$6,$B$1:$B$6,0))0,ROW(INDIRECT("1:"&ROWS($A $1:$A$6)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down "casey" wrote: I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Making list of unique items from two different columns
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "casey" wrote in message ... Your formula worked perfectly! I figured out what I had done wrong. I guess I had to sleep on it. :-) Thanks, casey "T. Valko" wrote: Here's a small sample file that demonstrates this. xExtractUniques.xls 16kb http://www.freefilehosting.net/download/3ee53 Sheet1 uses the same formulas I suggested in my other reply. Sheet2 uses formulas *based on the conditions* that there are no empty cells *within* the range and the data will *always* start on row 2 and rows will *never* be inserted above the range. These restrictive conditions allow us to use shorter more efficient formulas. The formulas on Sheet2 also use dynamic ranges so it's assumed the data will *always* be a contiguous range. -- Biff Microsoft Excel MVP "casey" wrote in message ... Biff, Thanks for your reply. I got the first formula to work fine. The second only gives me #NUM! I've tried several changes to no avail. To answer your questions...The only empty cells are rows on the bottom of the defined arrays rng1 is actually A2:A3000 and rng2 is C2:C3000. (I have an intermediate column.) Also, there will never be any new rows above the range. Thanks, casey "T. Valko" wrote: Try this: A1:An = rng1 B1:Bn = rng2 C1 = ABC Enter this array formula** in C2. This will return the count of items that meet the criteria: =COUNT(1/FREQUENCY(IF((rng1=C$1)*(rng2<""),MATCH(rng1&rng2 ,rng1&rng2,0)),ROW(rng2)-MIN(ROW(rng2))+1)) Enter this array formula** in D1 and copy down until you get blanks. You need to copy to a number of cells that is *at least equal to the number returned in cell C2*: =IF(ROWS(D$1:D1)<=C$2,INDEX(rng2,SMALL(IF((rng1=C$ 1)*(rng2<""),IF(MATCH(rng1&rng2,rng1&rng2,0)=ROW( rng2)-MIN(ROW(rng2))+1,ROW(rng2)-MIN(ROW(rng2))+1)),ROWS(D$1:D1))),"") These formulas account for empty cells. If there are no empty cells in your ranges then we can simplify the formulas and save some resources! Also, if your ranges are in set locations and you will *never* insert new rows above the range we can simplify the formulas to save some resources! ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "casey" wrote in message ... I have several unique formulas I am using for various purposes but none can solve what I need now. I have two columns (A & B) of items, 3000 rows deep. There are 15 unique entries in Col A. 150 unique entries in Col B. I want C1 to be a variable entry whereby I can enter one of the unique Col A items and return in Col D all the unique entries in Col B for what is entered in C1. For example: Col A Col B Col C Col D ABC 123A ABC 123A DEF 16LM 437F ABC 437F ABC 123A MNB 789H ABC 437F thnx, casey |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| tagging unique items in a list | Excel Worksheet Functions | |||
| Function to count unique items in list | Excel Worksheet Functions | |||
| count unique items in ever-growing list? | Excel Discussion (Misc queries) | |||
| Making list of items to truck monthly usage | Excel Discussion (Misc queries) | |||
| Making list with unique columns | Excel Worksheet Functions | |||
Linear Mode
