Counting unique entries across two or three columns
 

Hi,

I'm looking for a way to get a count of unique entries across multiple
columns of data. Say for example I had (with semi-colons indicating
the columns A B and C):
Smith;John;H
Smith;John;K
Smith;Catherine;
Jones;John;
Jones;Susan;L
Jones;Susan;B

I want to be able to get a count of:
(a) all the unique surnames/families (for the above example it's 2:
Smith and Jones). I think I have done this using a formula I found on
the web, which is =SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))
(b) all the unique combinations of surname and first name (in the
above example it's 4, Smith, John; Smith, Catherine; Jones, John and
Jones, Susan)
(c) all the unique combinations of surname, first name and middle
initial. However, I don't want to count those without an initial i.e.
where the cell is blank. So for the above example I want the answer 4:
Smith, John H; Smith, John, K; Jones, Susan L; Jones, Susan B).

I just need running counts of these different categories as I add data
and I don't want to use filters. Can this be done with functions? I'd
really appreciate any help.


Thanks very much,
Michelle

Counting unique entries across two or three columns
 

I'd use some helper cells.

=a1&";"&b1
and drag down

and another column of
=a1&";"&b1&";"&c1
and drag down.

Then use that formula to count uniques in those helper columns.

(use a character that doesn't show up in any of the name fields, though.)

wrote:

Hi,

I'm looking for a way to get a count of unique entries across multiple
columns of data. Say for example I had (with semi-colons indicating
the columns A B and C):
Smith;John;H
Smith;John;K
Smith;Catherine;
Jones;John;
Jones;Susan;L
Jones;Susan;B

I want to be able to get a count of:
(a) all the unique surnames/families (for the above example it's 2:
Smith and Jones). I think I have done this using a formula I found on
the web, which is =SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))
(b) all the unique combinations of surname and first name (in the
above example it's 4, Smith, John; Smith, Catherine; Jones, John and
Jones, Susan)
(c) all the unique combinations of surname, first name and middle
initial. However, I don't want to count those without an initial i.e.
where the cell is blank. So for the above example I want the answer 4:
Smith, John H; Smith, John, K; Jones, Susan L; Jones, Susan B).

I just need running counts of these different categories as I add data
and I don't want to use filters. Can this be done with functions? I'd
really appreciate any help.

Thanks very much,
Michelle

--

Dave Peterson

Counting unique entries across two or three columns
 

On Wed, 23 Jan 2008 14:35:41 -0800 (PST), wrote:

Hi,

I'm looking for a way to get a count of unique entries across multiple
columns of data. Say for example I had (with semi-colons indicating
the columns A B and C):
Smith;John;H
Smith;John;K
Smith;Catherine;
Jones;John;
Jones;Susan;L
Jones;Susan;B

I want to be able to get a count of:
(a) all the unique surnames/families (for the above example it's 2:
Smith and Jones). I think I have done this using a formula I found on
the web, which is =SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))
(b) all the unique combinations of surname and first name (in the
above example it's 4, Smith, John; Smith, Catherine; Jones, John and
Jones, Susan)
(c) all the unique combinations of surname, first name and middle
initial. However, I don't want to count those without an initial i.e.
where the cell is blank. So for the above example I want the answer 4:
Smith, John H; Smith, John, K; Jones, Susan L; Jones, Susan B).

I just need running counts of these different categories as I add data
and I don't want to use filters. Can this be done with functions? I'd
really appreciate any help.


Thanks very much,
Michelle

One way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english/index.htm

Then use these functions: (Surname,FN and MI refer to named ranges which are
at least as large as your ranges containing that data.

IMPORTANT**: The second and third formulas are ARRAY-FORMULAS and must be
entered with <ctrl<shift<enter. If you do that correctly, Excel will place
braces {...} around the formulas.

Surname =COUNTDIFF(Surname)
Surname+FN =COUNTDIFF(Surname&FN,,"")
SN+FN+MI =COUNTDIFF(IF(LEN(MI)0,Surname&FN&MI),,FALSE)

--ron

Counting unique entries across two or three columns
 

Hi,

I'm looking for a way to get a count of unique entries across multiple
columns of data. Say for example I had (with semi-colons indicating
the columns A B and C):
Smith;John;H
Smith;John;K
Smith;Catherine;
Jones;John;
Jones;Susan;L
Jones;Susan;B

Extending from Michelle's case, how to generate the counting table looks
like the following?

Smith Jones H I J K ....
John 2 1
Catherine 1 0
Susan 0 2
A
B
C
D
....

Counting unique entries across two or three columns
 

So there's really no way to do it without additional columns or a
download? I'm a bit reluctant to do that because I need to hand the
file over to someone else for data entry, so I'd really like something
that will keep recalculating without having to add additional software
or columns...


Cheers,
Michelle

Counting unique entries across two or three columns
 

Try this:

=IF(COUNTA(B2:C14)=SUM(1/COUNTIF(B2:C14,B2:C14)),"All Unique","Some
dupes")

Adjust ranges as appropriate, I assumed B2:C14

This is an array formula so Ctrl-Shift-Enter to commit


HTH,
JP

On Jan 28, 4:38*pm, wrote:
So there's really no way to do it without additional columns or a
download? I'm a bit reluctant to do that because I need to hand the
file over to someone else for data entry, so I'd really like something
that will keep recalculating without having to add additional software
or columns...

Cheers,
Michelle

Counting unique entries across two or three columns
 

On Mon, 28 Jan 2008 13:38:08 -0800 (PST), wrote:

So there's really no way to do it without additional columns or a
download? I'm a bit reluctant to do that because I need to hand the
file over to someone else for data entry, so I'd really like something
that will keep recalculating without having to add additional software
or columns...


Cheers,
Michelle

Well, it is possible to embed morefunc.xll in the workbook you hand over. That
is a menu option.
--ron

Counting unique entries across two or three columns
 

wrote...
I'm looking for a way to get a count of unique entries across
multiple columns of data. Say for example I had (with semi-colons
indicating the columns A B and C):
Smith;John;H
Smith;John;K
Smith;Catherine;
Jones;John;
Jones;Susan;L
Jones;Susan;B

Assume these entries were in A1:C6.

I want to be able to get a count of:
(a) all the unique surnames/families (. . .). I think I have done
this using a formula I found on the web, which is
SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))

That's the best formula for it.

(b) all the unique combinations of surname and first name (in the
above example it's 4, . . .)

Gets more complicated, but still possible with a single ARRAY formula
using only built-in functions.

=SUM(1/MMULT(--(A1:A6&";"&B1:B6=TRANSPOSE(A1:A6&";"&B1:B6)),
ROW(A1:A6)^0))

(c) all the unique combinations of surname, first name and middle
initial. However, I don't want to count those without an initial
i.e. where the cell is blank. So for the above example I want the
answer 4: . . .

Mor complicated still, but possible with another ARRAY formula.

=SUM(IF(C1:C6"",1/MMULT(--(A1:A6&";"&B1:B6&";"&C1:C6
=TRANSPOSE(A1:A6&";"&B1:B6&";"&C1:C6)),ROW(A1:A6)^ 0)))





I just need running counts of these different categories as I add data
and I don't want to use filters. Can this be done with functions? I'd
really appreciate any help.

Thanks very much,
Michelle

Counting unique entries across two or three columns
 

Yes! Thank you! That has done the trick very nicely.

Thanks so much for all the suggestions, I really appreciate them. Now
I just have to formulate the questions for all the other stuff I want
to do with these data! :-


All the best,
Michelle

Counting unique entries across two or three columns
 

Hi, I found this post very interesting and am very impressed with the
formulas provided, however I would like to ask for something extra
which I don't know if is actually possible (without VBA).

From the above post..

(b) all the unique combinations of surname and first name (in the
above example it's 4, . . .)

Gets more complicated, but still possible with a single ARRAY formula
using only built-in functions.

=SUM(1/MMULT(--(A1:A6&";"&B1:B6=TRANSPOSE(A1:A6&";"&B1:B6)), ROW(A1:A6)^0))

I would like a modification to so that only the unique combinations of
surname and first name where first name = John are returned (thus 2 in
the example).

Any help would be appraciated as I'm stuck :-)

Thanks.

Counting unique entries across two or three columns
 

Say you enter the first name to be used into D1, then try this *array*
formula:

=COUNT(1/FREQUENCY(IF((B1:B6=D1),MATCH(A1:A6,A1:A6,0)),ROW( 1:6)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
...
Hi, I found this post very interesting and am very impressed with the
formulas provided, however I would like to ask for something extra
which I don't know if is actually possible (without VBA).

From the above post..

(b) all the unique combinations of surname and first name (in the
above example it's 4, . . .)

Gets more complicated, but still possible with a single ARRAY formula
using only built-in functions.

=SUM(1/MMULT(--(A1:A6&";"&B1:B6=TRANSPOSE(A1:A6&";"&B1:B6)),
ROW(A1:A6)^0))

I would like a modification to so that only the unique combinations of
surname and first name where first name = John are returned (thus 2 in
the example).

Any help would be appraciated as I'm stuck :-)

Thanks.

Counting unique entries across two or three columns
 

On Feb 16, 6:58*pm, "RagDyer" wrote:
Say you enter the first name to be used into D1, then try this *array*
formula:

=COUNT(1/FREQUENCY(IF((B1:B6=D1),MATCH(A1:A6,A1:A6,0)),ROW( 1:6)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. *Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
wrote in message

...



Hi, I found this post very interesting and am very impressed with the
formulas provided, however I would like to ask for something extra
which I don't know if is actually possible (without VBA).

From the above post..

(b) all the unique combinations of surname and first name (in the
above example it's 4, . . .)

Gets more complicated, but still possible with a single ARRAY formula
using only built-in functions.

=SUM(1/MMULT(--(A1:A6&";"&B1:B6=TRANSPOSE(A1:A6&";"&B1:B6)),
ROW(A1:A6)^0))

I would like a modification to so that only the unique combinations of
surname and first name where first name = John are returned (thus 2 in
the example).

Any help would be appraciated as I'm stuck :-)

Thanks.- Hide quoted text -

- Show quoted text -

Thanks very much it worked really well!! However I would like to make
another modification which allows the function to evaluate every row
instead of a predefined list (e.g. 1 to 6).

I think I nearly have it but the last statement is causing problems
which contains ROW..

I put the following in a cell (e.g. F3) - =MATCH("zzzzzzzzzz",A:A)

=COUNT(1/
FREQUENCY(IF((B1:INDEX(B:B,F3)="John"),MATCH(A1:IN DEX(A:A,F3),A1:INDEX(A:A,F3),
0)),ROW(2:11)))

Thanks again.

Counting unique entries across two or three columns
 

Why not just oversize the formula?

=COUNT(1/FREQUENCY(IF((B1:B600="John"),MATCH(A1:A600,A1:A60 0,0)),ROW(1:600)))


Blanks shouldn't affect the accuracy.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

wrote in message
...
On Feb 16, 6:58 pm, "RagDyer" wrote:
Say you enter the first name to be used into D1, then try this *array*
formula:

=COUNT(1/FREQUENCY(IF((B1:B6=D1),MATCH(A1:A6,A1:A6,0)),ROW( 1:6)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !

wrote in message

...



Hi, I found this post very interesting and am very impressed with the
formulas provided, however I would like to ask for something extra
which I don't know if is actually possible (without VBA).

From the above post..

(b) all the unique combinations of surname and first name (in the
above example it's 4, . . .)

Gets more complicated, but still possible with a single ARRAY formula
using only built-in functions.

=SUM(1/MMULT(--(A1:A6&";"&B1:B6=TRANSPOSE(A1:A6&";"&B1:B6)),
ROW(A1:A6)^0))

I would like a modification to so that only the unique combinations of
surname and first name where first name = John are returned (thus 2 in
the example).

Any help would be appraciated as I'm stuck :-)

Thanks.- Hide quoted text -

- Show quoted text -

Thanks very much it worked really well!! However I would like to make
another modification which allows the function to evaluate every row
instead of a predefined list (e.g. 1 to 6).

I think I nearly have it but the last statement is causing problems
which contains ROW..

I put the following in a cell (e.g. F3) - =MATCH("zzzzzzzzzz",A:A)

=COUNT(1/
FREQUENCY(IF((B1:INDEX(B:B,F3)="John"),MATCH(A1:IN DEX(A:A,F3),A1:INDEX(A:A,F3),
0)),ROW(2:11)))

Thanks again.

Counting unique entries across two or three columns
 

If the ranges in question do start on row 1 then there is no problem (until
you insert new rows above the data!).

ROW needs to return an array from 1 to n that will match the output of
MATCH. One way to do that:

ROW(INDIRECT("1:"&F3))

Where F3 is the OP's MATCH formula that defines the end of range:

=MATCH("zzzzzzzzzz",A:A)

On a side note....

This is one of them there top reply to bottom post to top reply to etc. etc.
cluster_____s

<bg


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
Why not just oversize the formula?

=COUNT(1/FREQUENCY(IF((B1:B600="John"),MATCH(A1:A600,A1:A60 0,0)),ROW(1:600)))


Blanks shouldn't affect the accuracy.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

wrote in message
...
On Feb 16, 6:58 pm, "RagDyer" wrote:
Say you enter the first name to be used into D1, then try this *array*
formula:

=COUNT(1/FREQUENCY(IF((B1:B6=D1),MATCH(A1:A6,A1:A6,0)),ROW( 1:6)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !

wrote in message

...



Hi, I found this post very interesting and am very impressed with the
formulas provided, however I would like to ask for something extra
which I don't know if is actually possible (without VBA).

From the above post..

(b) all the unique combinations of surname and first name (in the
above example it's 4, . . .)

Gets more complicated, but still possible with a single ARRAY formula
using only built-in functions.

=SUM(1/MMULT(--(A1:A6&";"&B1:B6=TRANSPOSE(A1:A6&";"&B1:B6)),
ROW(A1:A6)^0))

I would like a modification to so that only the unique combinations of
surname and first name where first name = John are returned (thus 2 in
the example).

Any help would be appraciated as I'm stuck :-)

Thanks.- Hide quoted text -

- Show quoted text -

Thanks very much it worked really well!! However I would like to make
another modification which allows the function to evaluate every row
instead of a predefined list (e.g. 1 to 6).

I think I nearly have it but the last statement is causing problems
which contains ROW..

I put the following in a cell (e.g. F3) - =MATCH("zzzzzzzzzz",A:A)

=COUNT(1/
FREQUENCY(IF((B1:INDEX(B:B,F3)="John"),MATCH(A1:IN DEX(A:A,F3),A1:INDEX(A:A,F3),
0)),ROW(2:11)))

Thanks again.

Counting unique entries across two or three columns
 

So ... when are all the "misguided" going to wise up and just TOP POST?<bg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" wrote in message
...
If the ranges in question do start on row 1 then there is no problem (until
you insert new rows above the data!).

ROW needs to return an array from 1 to n that will match the output of
MATCH. One way to do that:

ROW(INDIRECT("1:"&F3))

Where F3 is the OP's MATCH formula that defines the end of range:

=MATCH("zzzzzzzzzz",A:A)

On a side note....

This is one of them there top reply to bottom post to top reply to etc. etc.
cluster_____s

<bg


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
Why not just oversize the formula?

=COUNT(1/FREQUENCY(IF((B1:B600="John"),MATCH(A1:A600,A1:A60 0,0)),ROW(1:600)))


Blanks shouldn't affect the accuracy.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

wrote in message
...
On Feb 16, 6:58 pm, "RagDyer" wrote:
Say you enter the first name to be used into D1, then try this *array*
formula:

=COUNT(1/FREQUENCY(IF((B1:B6=D1),MATCH(A1:A6,A1:A6,0)),ROW( 1:6)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !

wrote in message

...



Hi, I found this post very interesting and am very impressed with the
formulas provided, however I would like to ask for something extra
which I don't know if is actually possible (without VBA).

From the above post..

(b) all the unique combinations of surname and first name (in the
above example it's 4, . . .)

Gets more complicated, but still possible with a single ARRAY formula
using only built-in functions.

=SUM(1/MMULT(--(A1:A6&";"&B1:B6=TRANSPOSE(A1:A6&";"&B1:B6)),
ROW(A1:A6)^0))

I would like a modification to so that only the unique combinations of
surname and first name where first name = John are returned (thus 2 in
the example).

Any help would be appraciated as I'm stuck :-)

Thanks.- Hide quoted text -

- Show quoted text -

Thanks very much it worked really well!! However I would like to make
another modification which allows the function to evaluate every row
instead of a predefined list (e.g. 1 to 6).

I think I nearly have it but the last statement is causing problems
which contains ROW..

I put the following in a cell (e.g. F3) - =MATCH("zzzzzzzzzz",A:A)

=COUNT(1/
FREQUENCY(IF((B1:INDEX(B:B,F3)="John"),MATCH(A1:IN DEX(A:A,F3),A1:INDEX(A:A,F3),
0)),ROW(2:11)))

Thanks again.

Counting unique entries across two or three columns
 

when are all the "misguided" going to wise up and just TOP POST?

I don't think they have a choice at Google Groups (where the OP originated).

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
So ... when are all the "misguided" going to wise up and just TOP
POST?<bg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" wrote in message
...
If the ranges in question do start on row 1 then there is no problem
(until
you insert new rows above the data!).

ROW needs to return an array from 1 to n that will match the output of
MATCH. One way to do that:

ROW(INDIRECT("1:"&F3))

Where F3 is the OP's MATCH formula that defines the end of range:

=MATCH("zzzzzzzzzz",A:A)

On a side note....

This is one of them there top reply to bottom post to top reply to etc.
etc.
cluster_____s

<bg


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
Why not just oversize the formula?

=COUNT(1/FREQUENCY(IF((B1:B600="John"),MATCH(A1:A600,A1:A60 0,0)),ROW(1:600)))


Blanks shouldn't affect the accuracy.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

wrote in message
...
On Feb 16, 6:58 pm, "RagDyer" wrote:
Say you enter the first name to be used into D1, then try this *array*
formula:

=COUNT(1/FREQUENCY(IF((B1:B6=D1),MATCH(A1:A6,A1:A6,0)),ROW( 1:6)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!

wrote in message

...



Hi, I found this post very interesting and am very impressed with the
formulas provided, however I would like to ask for something extra
which I don't know if is actually possible (without VBA).

From the above post..

(b) all the unique combinations of surname and first name (in the
above example it's 4, . . .)

Gets more complicated, but still possible with a single ARRAY formula
using only built-in functions.

=SUM(1/MMULT(--(A1:A6&";"&B1:B6=TRANSPOSE(A1:A6&";"&B1:B6)),
ROW(A1:A6)^0))

I would like a modification to so that only the unique combinations of
surname and first name where first name = John are returned (thus 2 in
the example).

Any help would be appraciated as I'm stuck :-)

Thanks.- Hide quoted text -

- Show quoted text -

Thanks very much it worked really well!! However I would like to make
another modification which allows the function to evaluate every row
instead of a predefined list (e.g. 1 to 6).

I think I nearly have it but the last statement is causing problems
which contains ROW..

I put the following in a cell (e.g. F3) - =MATCH("zzzzzzzzzz",A:A)

=COUNT(1/
FREQUENCY(IF((B1:INDEX(B:B,F3)="John"),MATCH(A1:IN DEX(A:A,F3),A1:INDEX(A:A,F3),
0)),ROW(2:11)))

Thanks again.

Counting unique entries across two or three columns
 

On 16 Feb, 23:43, "T. Valko" wrote:

Yes, they have. Google Groups lets them post either at the top ...

I don't think they have a choice at Google Groups (where the OP originated).

.... or in the middle ...

--
Biff
Microsoft Excel MVP

"RagDyeR" wrote in message

...

So ... when are all the "misguided" going to wise up and just TOP
POST?<bg

.... or Google Groups lets them post at the bottom, or any
combination.
--
David Biddulph
[posting using Gooogle Groups this time, to demonstrate]

Counting unique entries across two or three columns
 

"T. Valko" wrote...
when are all the "misguided" going to wise up and just TOP POST?

I don't think they have a choice at Google Groups (where the OP
originated).
....

There's always a choice. It's more a matter of the default behavior of
the user's newsreader and their laziness.

Clearly OE users are an extremely lazy bunch.