Display number of Unique values in one row based on criteria in a different row

[hossa_dude]

Long topic, I know ;-))

Ok...here's what I want to do:
I want to count the number of unique values in one row when a specifi
criteria in a different row is meet.
More specs?
Ok...assume in Column A you have a list of different people
and in column B you have different languages

John.....English
Joe.....English
Joe....German
Tina....English
John...French
Mike....German
Lisa....English
etc.

What I want to do is...for example I want to know how many differen
people do speak English....or - other way round count how man
languages are spoken by John.....

Anyone to the rescue........I'm puzzled.....
I know how to count unique values but not the link to the othe
row.......please help......

Cheers Jo

 

[Frank Kabel]

hi
though this can be done with formulas I'd suggest you use pivot tables
for this kind of evaluation. See the following sites for getting
started with them.
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


in your case you may use the language as row item and countof Name as
data item

 

[hossa_dude]

Hi Frank,
thanks first of all.
The issue with Pivot tables is, that the input data will be used t
generate a weekly or monthly report.
Therefore, eachtime the report will have to be generated a new data-se
arrives that will have to be transformed in a Pivot Table.
As this is way a lot of work and the person that needs to do that doe
not have real Excel expertiese I wanted to use formulars.
So each time a new data-set arrives the formula just needs to b
copied......

unfortunately, pivot tables are no good....
Any help on the formulas please??

kind regards
Joer

 

[Frank Kabel]

Hi
you can setup a dynamic range for the pivot table data source. So new
data is included in the pivot table. See
http://www.contextures.com/xlPivot01.html

IMHO pivot tables are in this case much easier than copying formulas
and especially for someone not so familiar with Excel this should be
easier than using formulas

 

[hossa_dude]

Ok...maybe you're right.....I'll have a look and keep you posted abou
my proceedings
Thanks again
Joer

 

[Harlan Grove]

...
...

Ok...assume in Column A you have a list of different people
and in column B you have different languages

John.....English
Joe.....English
Joe....German
Tina....English
John...French
Mike....German
Lisa....English
etc.

What I want to do is...for example I want to know how many different
people do speak English....or - other way round count how many
languages are spoken by John.....

If the column of names were named Names and the corresponding column of
languages were named Languages, then you could find the number of English
speakers using the array formula

=COUNT(1/FREQUENCY(IF(Languages="English",MATCH(Names,Names,0)),
ROW(INDIRECT("1:"&COUNTA(Names)))))

and the number of languages John speaks using the array formula

=COUNT(1/FREQUENCY(IF(Names="John",MATCH(Languages,Languages,0)),
ROW(INDIRECT("1:"&COUNTA(Languages)))))

 

[hossa_dude]

Hi Harlan,

thanks....I believe that nearly does it.....I was kind of struggelin
with the pivot tables as my input date is quite large (e.g. currentl
1600+ datasets with 28 columns....).

Anyway.....the formular provided by you does it as i said...wel
nearly.....

Is there any chance to use wildcards in that formular or do I alway
have to specify a dedicated string to look for?
E.g. use "Jo*" instead of "John" and/or "Joe"?
This does not really make sense in the context given below but it wil
in my actual case...
So please....help again....thanks in advance

Kind regards
Joer

 

[Frank Kabel]

Hi
try
=COUNT(1/FREQUENCY(IF(ISNUMBER(FIND("Joe",Names)),MATCH(Languages,Langu
ages,0)),
ROW(INDIRECT("1:"&COUNTA(Languages)))))

 

[hossa_dude]

Frank....you're a genius...!!

Thank you so much....that formular finally did the trick!!

Perfect....I'm as happy as can be!

Thank you again.....!!

 

[hossa_dude]

Hi there,

I'm terribly sorry that I have to come back again.
As stated above the formula works just fine.....however, next level o
the issue.....:-(

Was it sufficient to have two criterias in the example above (Name an
Language) I'm wondering what the formula will have to look like whe
adding a third criteria (e.g. Country).
In detail I'm trying to do the following:
Based on two criterias e.g. name and language I' trying to count th
unique values in a third row (e.g. country).
Not hard enough?.... In one of the criterias I'm looking for
specific string (as above e.g. Names containing "Jo").
I know this is kind of hard to describe it.....
If you could help me just once again, I'd be really really happy. I
case you need a more precise description just drop me a note...

Many thanks in advance...
Joer

 

[Harlan Grove]

...
...

In detail I'm trying to do the following:
Based on two criterias e.g. name and language I' trying to count the
unique values in a third row (e.g. country).
Not hard enough?.... In one of the criterias I'm looking for a
specific string (as above e.g. Names containing "Jo").
I know this is kind of hard to describe it.....
If you could help me just once again, I'd be really really happy. In
case you need a more precise description just drop me a note...

...

=COUNT(1/FREQUENCY(IF(ISNUMBER(FIND("Jo",Names))*(Country="Clowd Coockoo Land"),
MATCH(Languages,Languages,0)),ROW(Languages)^0))

 

[hossa_dude]

Hmm...thanks Harlan....
however, if I apply the formula to my environment, the result i
delivers is 1.....which is not correct....:-(
Can't see where the mistake is....

 

[hossa_dude]

Well I think, I got it....
I simply modified the formula like this:

 

[Harlan Grove]

Well I think, I got it....
I simply modified the formula like this:

...

You got it right. I spaced out about the second argument to frequency. In other
words, I screwed up by not testing and not reading my previous response.

 

[hossa_dude]

No...you didn't screw up...you're a big help...

Anyway.....any idea on how to modify the formula in the way that i
looks for a specfic occurance of a string in the "country" as well
Rather than for a specific country name? e.g. look for the occurance o
the string "Coockoo" rather than "Clowd Coockoo Land" and nothin
else?

Kind regards Joer

 

[hossa_dude]

Okok....got it solved myself.....call me master of copy & paste
Thank you guys...you've been a great help....

The final formula looks like this:
=COUNT(1/FREQUENCY(IF(ISNUMBER(FIND("Jo",Names))*(ISNUMBER(FIND(Country="Coockoo")))
MATCH(Languages,Languages,0)),ROW(INDIRECT("1:"&COUNTA(Languages))))