Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Count Unique text in 3 different columns
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10
Default Count Unique text in 3 different columns
Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,276
Default Count Unique text in 3 different columns
Hi,
what are the results you are looking for ?

"Vitordf" wrote:

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11,501
Default Count Unique text in 3 different columns
Hi,

I'm not sure if you want each column or a single count for the 3 columns.
Try these ARRAY formula

All Columns
=SUM(IF(LEN(A1:C23),1/COUNTIF(A1:C23,A1:C23)))

A single column. Drag right for cols B & C
=SUM(IF(LEN(A1:A23),1/COUNTIF(A1:A23,A1:A23)))

These are array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Vitordf" wrote:

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10
Default Count Unique text in 3 different columns
Hi, Sorry, I am looking to have the Unique Number of Names (Column B) where
they are Ternured or New Hire (Column C) for each of the Categories (Column
A) as per example would be something like;
Ternured - DC&D - 3 Names
New Hire - DC&D - 2 Names
Ternured - AIO - 1 Name
New Hire - AIO - 1 Name

There is also a possibility that 1 Name appear in both Categories (Column A),

Hope I have been a bit more clear,
Many Thanks for the help

"Eduardo" wrote:

Hi,
what are the results you are looking for ?

"Vitordf" wrote:

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10
Default Count Unique text in 3 different columns
Hi Mike,

Many Thanks for your reply and help, and the examples are great but I need
to go futher, I need to ascertain the number of Agents (Column B) for each of
the Categories in column A and C and were the same agent can be in both
category in Column A... in fact I need to get the Total Unique Names of New
Hire/Ternured for each of the Category DC&D/AIO.

Regs,

"Mike H" wrote:

Hi,

I'm not sure if you want each column or a single count for the 3 columns.
Try these ARRAY formula

All Columns
=SUM(IF(LEN(A1:C23),1/COUNTIF(A1:C23,A1:C23)))

A single column. Drag right for cols B & C
=SUM(IF(LEN(A1:A23),1/COUNTIF(A1:A23,A1:A23)))

These are array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Vitordf" wrote:

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,718
Default Count Unique text in 3 different columns
=SUM(N(FREQUENCY(MATCH(A1:A23&B1:B23&C1:C23,A1:A23 &B1:B23&C1:C23,0),MATCH(A1:A23&B1:B23&C1:C23,A1:A2 3&B1:B23&C1:C23,0))0))


"Vitordf" wrote:

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,718
Default Count Unique text in 3 different columns
Assuming your data in a1:c23

criteria
E1: DC&D F1: Ternure
E2: DC&D F2: New Hire
and so on...

G1:
=SUM(N(FREQUENCY(IF(($A$1:$A$23=E1)*($C$1:$C$23=F1 ),MATCH($B$1:$B$23,$B$1:$B$23,)),MATCH($B$1:$B$23, $B$1:$B$23,))0))

ctrl+shift+enter, not just enter
copy down as far as needed


"Vitordf" wrote:

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10
Default Count Unique text in 3 different columns
Hi,

Again many Thanks, and yes the formula you gave me is great and very useful
for other applications I have, but for this particular exercise I need to
have the result as:

DC&D AIO
New Hire 2 1
Ternure 3 1

I need to ascertain the Number of New Hires and Ternured in each category.

Thanks,

"Teethless mama" wrote:

=SUM(N(FREQUENCY(MATCH(A1:A23&B1:B23&C1:C23,A1:A23 &B1:B23&C1:C23,0),MATCH(A1:A23&B1:B23&C1:C23,A1:A2 3&B1:B23&C1:C23,0))0))


"Vitordf" wrote:

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10
Default Count Unique text in 3 different columns
many Thanks for your GREAT Help, this is exactly what I was looking for, you
have saved a lot of working hours... :-))

"Teethless mama" wrote:

Assuming your data in a1:c23

criteria
E1: DC&D F1: Ternure
E2: DC&D F2: New Hire
and so on...

G1:
=SUM(N(FREQUENCY(IF(($A$1:$A$23=E1)*($C$1:$C$23=F1 ),MATCH($B$1:$B$23,$B$1:$B$23,)),MATCH($B$1:$B$23, $B$1:$B$23,))0))

ctrl+shift+enter, not just enter
copy down as far as needed


"Vitordf" wrote:

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,180
Default Count Unique text in 3 different columns
Excel 2007 PivotTable
One formula used:
=1/COUNTIF([Name],[Name])
http://c0718892.cdn.cloudfiles.racks.../04_06_10.xlsx
Pdf preview:
http://www.mediafire.com/file/yim4emdgnum/04_06_10.pdf

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Count unique values across two columns with multiple conditions Ert Excel Worksheet Functions 4 March 8th 09 12:22 AM
Count Unique accross mulitple columns Rob Drummond, Jr Excel Worksheet Functions 7 January 13th 09 03:19 PM
count unique in one column based on two other columns casey Excel Worksheet Functions 4 November 21st 08 11:21 PM
count unique instances based on two columns [email protected] Excel Worksheet Functions 9 May 27th 07 01:49 AM
Count unique if text Mike Fogleman Excel Worksheet Functions 16 January 21st 07 07:47 PM


All times are GMT +1. The time now is 05:47 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement