Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 10th 08, 02:51 AM posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 972
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old November 10th 08, 09:58 PM posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 972
Default 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   Report Post  
Old November 10th 08, 10:19 PM posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 972
Default 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   Report Post  
Old 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
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Unique Values with Duplicates DOUG ECKERT[_2_] Excel Discussion (Misc queries) 0 May 8th 08 03:00 PM
How do I sum unique values among duplicates horizontally Janine Ball Excel Worksheet Functions 2 August 10th 07 12:54 PM
How to remove duplicates and combine their unique values? Dave Excel Discussion (Misc queries) 3 June 11th 07 07:51 AM
Sum duplicates unique values and delete dupliques Dave Excel Worksheet Functions 1 June 7th 07 01:02 PM
check for duplicates, then sum unique values Weissme Excel Discussion (Misc queries) 0 August 9th 06 04:35 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017