Remember Me?

#1
November 10th 08, 02:51 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 972
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
November 10th 08, 03:47 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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
November 10th 08, 09:58 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 972
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
November 10th 08, 10:19 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 972
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
November 10th 08, 10:43 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post DOUG ECKERT[_2_] Excel Discussion (Misc queries) 0 May 8th 08 03:00 PM Janine Ball Excel Worksheet Functions 2 August 10th 07 12:54 PM Dave Excel Discussion (Misc queries) 3 June 11th 07 07:51 AM Dave Excel Worksheet Functions 1 June 7th 07 01:02 PM Weissme Excel Discussion (Misc queries) 0 August 9th 06 04:35 PM

All times are GMT +1. The time now is 06:51 AM.