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

  • Thread starter Thread starter hossa_dude
  • Start date Start date
H

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
 
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
 
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
 
Ok...maybe you're right.....I'll have a look and keep you posted abou
my proceedings :)
Thanks again
Joer
 
...
...
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.....
Click to expand...

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)))))
 
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
 
Hi
try
=COUNT(1/FREQUENCY(IF(ISNUMBER(FIND("Joe",Names)),MATCH(Languages,Langu
ages,0)),
ROW(INDIRECT("1:"&COUNTA(Languages)))))
 
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.....!!
 
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
 
...
...
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...
Click to expand...
...

=COUNT(1/FREQUENCY(IF(ISNUMBER(FIND("Jo",Names))*(Country="Clowd Coockoo Land"),
MATCH(Languages,Languages,0)),ROW(Languages)^0))
 
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....
 
Well I think, I got it....
I simply modified the formula like this:
Click to expand...
...

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.
 
:) 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
 
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))))
 
Back
Top