caseman22
asked on
Lookup mutiple names and generate reponse based on number
I have a spreadsheet that has 2 sheets. The first sheet has ColA with a list of server names and ColB is the first day of the month. I want to take the server name in A7 and see if it appears in sheet 2. If that server name appears anywhere on sheet 2 with an error code greater than 1 put F in ColB sheet 1 otherwise put a S in ColB sheet 1. What is the best way to accomplish this. Thank you in advance for your help.
try in sheet1!colB, assuming your sheet2 is called sheet2, that the server names in sheet2 are in col a and the error codes in col B.
=if(sumproduct((A7=Sheet2! $A$1:$A$10 00)*(Sheet 2!$B$2:$B$ 1000>1)*1) >1,"f","s" )
Thomas
=if(sumproduct((A7=Sheet2!
Thomas
the formula should be
=if(sumproduct((A7=Sheet2! $A$2:$A$1000)*(Sheet2!$B$2:$B$ 1000>1)*1) >1,"f","s" )
see attached file for my assumptions
23923296.xls
=if(sumproduct((A7=Sheet2!
see attached file for my assumptions
23923296.xls
ASKER
here is my sample file. Thanks.
Nutsch, you formula doesnt seem to give the results neccessary. i dont want to add them, if status code for server is greater than 1 anywhere on 2nd sheet then the day should have an f for failed. thanks for the input though.
ExampleXLS.xls
Nutsch, you formula doesnt seem to give the results neccessary. i dont want to add them, if status code for server is greater than 1 anywhere on 2nd sheet then the day should have an f for failed. thanks for the input though.
ExampleXLS.xls
Hi,
I have one quick query...in your raw-data i dont see a day option...then how you are picking the same in the final output..???
I have one quick query...in your raw-data i dont see a day option...then how you are picking the same in the final output..???
I don't use sumproduct as a sum, I use it as a validator for multiple conditions. Since it's in an if structure, it returns either "s" or "f", so I don't really understand your comment.
Thomas
Thomas
ASKER
saurabh726, the day is just to show the outcome. i guess i would select the top cell of todays date then run the macros you were suggesting. there would not be any funtion cased on date the 2nd sheet will change tomorrow with data pasted from backup software then i would move over to day 2 to let the macro or formula work. thanks
nutsch, sorry i didnt mean to say sum. the formula did not give me the expecte results plus the formula in the response and in the sheet you provided are different.
nutsch, sorry i didnt mean to say sum. the formula did not give me the expecte results plus the formula in the response and in the sheet you provided are different.
One quick suggestion...if you enter the date option out there in your raw-data your works get easy since you can pick it up directly...otherwise you have to select your range everytime that you wanted to check in macro..let me know whats conveninent or what you want to do....so that accordingly we can move forwaded...
ASKER
we can put in the date in a seperate column in sheet2. Thank you.
ASKER
Here is an updated example file.
ASKER
forgot to click add, :( one of those days.
ExampleXLS.xls
ExampleXLS.xls
Got it:
=IF(SUMPRODUCT(($A2=Result s!$A$2:$A$ 1000)*(Res ults!$C$2: $C$1000=DA TEVALUE(B$ 1 & " " & $A$1 & " 2008"))*(Results!$B$2:$B$1 000>1)*1)> 0,"f","s")
Will work for all of 2008.
Thomas
=IF(SUMPRODUCT(($A2=Result
Will work for all of 2008.
Thomas
ASKER
Thomas, it still only takes the first instance in account. for example:
server1 ended with status 0 in row 2 and status 150 in row 3. Therefore the overall status should be F but in your formula it is still S. Also, if i put the formula on Nov sheet cell b2 the results show up in c2. thank you,
Joe
server1 ended with status 0 in row 2 and status 150 in row 3. Therefore the overall status should be F but in your formula it is still S. Also, if i put the formula on Nov sheet cell b2 the results show up in c2. thank you,
Joe
does it say s because it's looking for January and your Results tab only has November?
check attached file and see if that works. .
Thomas
23923296.xls
check attached file and see if that works. .
Thomas
23923296.xls
ASKER
Thomas, that works great! thank you. Is it possible to make the formula say NA if the server name doesnt show in the results tab or the results from that date dont show in the results sheet. I Dont want to create any false information. For example if i delete the server name out of results sheet the Nov tab day still says "s" and if the Nov cell is a future date is says "s" instead it should say "NA" or something. Great stuff!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think that does it Thomas. Let me do full testing over the weekend and get back to you.
You are the man!
Thanks,
Joe
You are the man!
Thanks,
Joe
Thanks Joe, but I need to correct, I'm just one of the men here, there are quite a few others, not so many ladies though.
Thomas
Thomas
ASKER
Thomas, your solotions works excelent.
Thank you very much.
I will definaltely use this one again.
Thank you very much.
I will definaltely use this one again.
ASKER
Thomas was VERY helpful and knowledgable. Thank you.
Thanks for the grade and the compliment,
Thomas
Thomas
The best way i can suggest you to this is by macros...If you can upload your sample file then can surely help to develop a macro to do what you are looking for....
Saurabh...