Link to home
Start Free TrialLog in
Avatar of caseman22
caseman22Flag for United States of America

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.
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Hi,

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...
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$1000)*(Sheet2!$B$2:$B$1000>1)*1)>1,"f","s")

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
Avatar of caseman22

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
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 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
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.
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...
we can put in the date in a seperate column in sheet2. Thank you.
Here is an updated example file.
forgot to click add, :( one of those days.
ExampleXLS.xls
Got it:

=IF(SUMPRODUCT(($A2=Results!$A$2:$A$1000)*(Results!$C$2:$C$1000=DATEVALUE(B$1 & " " & $A$1 & " 2008"))*(Results!$B$2:$B$1000>1)*1)>0,"f","s")

Will work for all of 2008.

Thomas
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
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
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
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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, your solotions works excelent.

Thank you very much.

I will definaltely use this one again.
Thomas was VERY helpful and knowledgable. Thank you.
Thanks for the grade and the compliment,
Thomas