Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying unique values among duplicates
If sheet2 cols A and C match the value in sheet1 cols H and I, I want to
return the number of unique values in sheet 1 column A. Can anyone provide a formula for accomplishing this: The following example should yield a result of 2 for the Arenas/Bibby combination because there are two rows in sheet1 where Arenas is in colH and Bibby in colI, AND the date in colA is unique (10/29/08 and 10/30/08): sheet2 colA colC Arenas Bibby sheet1 colA colH colI 10/29/08 Butler Simmons 10/29/08 Arenas Bibby 10/29/08 Thomas Brown 10/30/08 Arenas Bibby 10/30/08 Stevens Hale 10/30/08 Arenas Bibby 10/30/08 Stevens Simmons 10/31/08 Arenas Simmons Thanks, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying unique values among duplicates
Try this array formula** entered on Sheet1:
=COUNT(1/FREQUENCY(IF((H2:H9=Sheet2!A1)*(I2:I9=Sheet2!C1),A 2:A9),A2:A9)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "bob" wrote in message ... If sheet2 cols A and C match the value in sheet1 cols H and I, I want to return the number of unique values in sheet 1 column A. Can anyone provide a formula for accomplishing this: The following example should yield a result of 2 for the Arenas/Bibby combination because there are two rows in sheet1 where Arenas is in colH and Bibby in colI, AND the date in colA is unique (10/29/08 and 10/30/08): sheet2 colA colC Arenas Bibby sheet1 colA colH colI 10/29/08 Butler Simmons 10/29/08 Arenas Bibby 10/29/08 Thomas Brown 10/30/08 Arenas Bibby 10/30/08 Stevens Hale 10/30/08 Arenas Bibby 10/30/08 Stevens Simmons 10/31/08 Arenas Simmons Thanks, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying unique values among duplicates
Biff,
This works very well and yields the correct answer when I set up the formula in sheet1, but how would I set up the formula in sheet2 (which is where I really want it)? Bob "T. Valko" wrote: Try this array formula** entered on Sheet1: =COUNT(1/FREQUENCY(IF((H2:H9=Sheet2!A1)*(I2:I9=Sheet2!C1),A 2:A9),A2:A9)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "bob" wrote in message ... If sheet2 cols A and C match the value in sheet1 cols H and I, I want to return the number of unique values in sheet 1 column A. Can anyone provide a formula for accomplishing this: The following example should yield a result of 2 for the Arenas/Bibby combination because there are two rows in sheet1 where Arenas is in colH and Bibby in colI, AND the date in colA is unique (10/29/08 and 10/30/08): sheet2 colA colC Arenas Bibby sheet1 colA colH colI 10/29/08 Butler Simmons 10/29/08 Arenas Bibby 10/29/08 Thomas Brown 10/30/08 Arenas Bibby 10/30/08 Stevens Hale 10/30/08 Arenas Bibby 10/30/08 Stevens Simmons 10/31/08 Arenas Simmons Thanks, Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying unique values among duplicates
Never mind. I figured out how to do it. Thanks for your help.
Bob "bob" wrote: If sheet2 cols A and C match the value in sheet1 cols H and I, I want to return the number of unique values in sheet 1 column A. Can anyone provide a formula for accomplishing this: The following example should yield a result of 2 for the Arenas/Bibby combination because there are two rows in sheet1 where Arenas is in colH and Bibby in colI, AND the date in colA is unique (10/29/08 and 10/30/08): sheet2 colA colC Arenas Bibby sheet1 colA colH colI 10/29/08 Butler Simmons 10/29/08 Arenas Bibby 10/29/08 Thomas Brown 10/30/08 Arenas Bibby 10/30/08 Stevens Hale 10/30/08 Arenas Bibby 10/30/08 Stevens Simmons 10/31/08 Arenas Simmons Thanks, Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying unique values among duplicates
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "bob" wrote in message ... Never mind. I figured out how to do it. Thanks for your help. Bob "bob" wrote: If sheet2 cols A and C match the value in sheet1 cols H and I, I want to return the number of unique values in sheet 1 column A. Can anyone provide a formula for accomplishing this: The following example should yield a result of 2 for the Arenas/Bibby combination because there are two rows in sheet1 where Arenas is in colH and Bibby in colI, AND the date in colA is unique (10/29/08 and 10/30/08): sheet2 colA colC Arenas Bibby sheet1 colA colH colI 10/29/08 Butler Simmons 10/29/08 Arenas Bibby 10/29/08 Thomas Brown 10/30/08 Arenas Bibby 10/30/08 Stevens Hale 10/30/08 Arenas Bibby 10/30/08 Stevens Simmons 10/31/08 Arenas Simmons Thanks, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Values with Duplicates | Excel Discussion (Misc queries) | |||
How do I sum unique values among duplicates horizontally | Excel Worksheet Functions | |||
How to remove duplicates and combine their unique values? | Excel Discussion (Misc queries) | |||
Sum duplicates unique values and delete dupliques | Excel Worksheet Functions | |||
check for duplicates, then sum unique values | Excel Discussion (Misc queries) |