Lookup Exact functions?

[Roge]

Hi, am trying to lookup and match values [am using Excel 200]
a simplified example of my data -

A B C
1 code name
2 MG1 Name1
3 MG2 Name2
4 MG1 Name3

I am using SUM(COUNTIF(C2C4, etc to add the number of certain names in
column C, the difficulty I have is trying to subtract the number of those
that meet certain criteria, the aim is -

1 search column C for a specified name [eg. name1]
2 lookup code in column B for name1 [MG1]
3 then search column B for an exact match for that code [MG1]
4 read name for that exact matched code in column C [name 3]
5 and count if name 3 is a "sepcified name/text"
then subtract from the original total

Essentially I am trying to account for repetitions where name1 and name3 are
different though should be counted as the same when their repective codes are
identical. I imagine I may need to use a number of functions in my formula
here. Any help with this would be great, many thanks, Roge

 

[Dave]

Hi Roge,
Not quite sure if I understand - but I'll have a go...
"Essentially I am trying to account for repetitions "
Will just counting unique values in column B give you what you want?
You can do this with filters, or a function, such as:
=SUMPRODUCT((B2:B4<>"")/(COUNTIF(B2:B4,B2:B4)+(B2:B4="")))
Change the ref's to suit your data.
Regards - Dave.

 

[Roge]

Hi Dave, thanks for quick reply.

sorry not sure I explained it that well. what I'm trying to do is count the
values[names of organisms from culture] that are also represented by a
different value[as the data may include that organism name as part of a
larger group name] - they need to be from the same culture specimen [same
code in column B]

ie look for positive cultures that aren't counted twice as they may be
represented by more than one name

therefore the formula should - identify specific names in column C and check
that there are also not other specific names with the same code

unfortunately counting uniques values in column B wouldn't consider whether
the names with identical codes are those that would also be represented by a
larger group name with the same code

hope this makes some sense, thanks, Roge

 

[Dave]

Hi Roge,
I must have my thicko cap on tonight, because I still can’t understand what
you are trying to count, or the criteria for that count. If you want to
persevere, could you please provide a larger data sample example – say 10
rows – and tell me again what you want to achieve, AND what answer you would
get from the data sample you give.
I am sure that once I understand, I will look back on your previous
explanations and wonder why I didn’t get it straight away!
Regards – Dave.

 

[Roge]

Hi Dave, thanks for trying with this, sorry it didnt make sense. ok im
going to try again -
data sample example

A B
code name
1 MG1 abc
2 MG2 d
3 MG3 e
4 MG1 f
5 MG5 g
6 MG6 h
7 MG1 a
8 MG8 j
9 MG9 abc
10 MG10 a

where "abc" name refers to a group which includes the names "a","b" and "c"
I have a COUNTIF formula for counting the number of times certain names, say
"abc","d","e" and "a", occur [=5 above]

I would like to subtract the number of times "a","b" or "c" occur when their
code is the same as the code for any "abc", as they would already be
represented by that name, answer=1 above [row 1 "abc" and row 7 "a" both
contain "a" and have the same code, whereas row 10 "a" has a different code
so doesnt need to be subtracted]

I can't work out how to write a formula for this subtraction, maybe it is
too difficult. I think the formula needs to search for specific names[values]
and lookup exact matching codes for those names. hope this helps, thanks
again, Roge

 

[Dave]

Hi Roge,
Sorry, but I think this one's beyond me. I got close, but in the end my head
hurt.
There are people much more skilled in functions in these groups, so try
reposting your question using the last description you sent me.

Regards - Dave.

 

[Roge]

Hi Dave,
Thanks for your help anyway, much appreciated
Roge