Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup Exact functions?
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup Exact functions?
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup Exact functions?
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup Exact functions?
Hi Roge,
I must have my thicko cap on tonight, because I still cant 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 didnt get it straight away! Regards €“ Dave. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup Exact functions?
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup Exact functions?
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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup Exact functions?
Hi Dave,
Thanks for your help anyway, much appreciated Roge "Dave" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find exact value using LOOKUP | Excel Discussion (Misc queries) | |||
Need to do an EXACT LOOKUP | Excel Worksheet Functions | |||
Match - Exact - Lookup? | Excel Worksheet Functions | |||
LookUp - Exact only | Excel Worksheet Functions | |||
LookUp - Exact only | Excel Worksheet Functions |