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

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

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

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
....

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

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

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

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

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

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.

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.

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.