+ Reply to Thread
Results 1 to 9 of 9

How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply no

  1. #1
    Registered User
    Join Date
    05-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2007 / Excel 2013
    Posts
    6

    How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply no

    Ok so first off.. my first post.. new member so ill say hi here for now and find the checkin forum later...


    ok so i have an xls now that is pretty simple but keeping it simple requires so many repetitive tasks...

    this part will be overal why and how for info then off to the needed help.

    i can give this to you in a nutshell.
    We use Altiris Client Management suite to manage our computers and it does a nice job. We just upgraded to the latest version and now we found an issue. a sql table that records information has a bug and there is a fix. well that fixes that for us but we lost the installed records for some machines. There are other tables that have this information too but different pieces.

    so I took an xls and did this.

    1. took computers and put them in row a of sheet 1
    2. ran sql query against the table to get individual software like MS13-001, MS13-002, etc.
    3. placed all of those into new sheets
    4. back on sheet 1 i made the rows of ms13-001(b1), ms13-002(b2), etc

    for say ms13-001 I went to cell B2 as the first computer name starts there and I said

    =IF(ISNA(VLOOKUP(A2,'MS13-001'!A:A,1,FALSE)),"No","Yes")

    drug that down the list for all cells in that row.. this works great.. even setup the conditional formatting for yes = green and no = orange.

    now this worked but is out dated now and I have oh say 50-60 sheets to update..

    so what I would like to do is this..

    start over but have sheet 1 the same as above.
    take sheet 2 and drop the entire list of all the software

    what I cant figure out is how to then back on sheet 1 is to put a yes for say....

    if computer listed in A2 is in sheet 2 row a
    and
    row b contains MS13-001 (i say that because microsoft has so many .exe and .msu so would be easier to do a contains ???)
    row c contains success

    so hopefully I didnt loose ya there..


    Machine Name AdvertisementName Status End
    MXL1340GKB Windows6.1-KB2769369-x86.msu for MS13-001 Command executed 2013-01-16 03:02:54.000

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply n

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    05-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2007 / Excel 2013
    Posts
    6

    Re: How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply n

    Thank you very much. Ok lets see if this has enough information..

    attached has 3 sheets.
    Overview
    Overview Breakdown
    Full Report

    __________________________________
    Overview - would like it to show a count of unique computers from full report that have installed so success only but if a computer lists it 5 times only want to count it once
    Overview Breakdown - lists each computer and yes/no if they received a success of the patch
    Full Report - page for me to just paste in the report after I run it with no modifications


    so I was able to do this with simply breaking down my sheets to the specific patch but that takes too long and especially when you have a ton of them. to update takes forever. would be nice to just paste into one sheet and have it calculate them all.

    so i just dont know how to do the yes/no when a computer is in row A, patch in row B, and Success in row D.


    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply n

    do you mean something like this?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2007 / Excel 2013
    Posts
    6

    Re: How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply n

    looks good.. ill try and put my full report in and try and get real computer names in there to see.. but yes that appears to be the multi rows im looking to do on this.

  6. #6
    Registered User
    Join Date
    05-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2007 / Excel 2013
    Posts
    6

    Re: How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply n

    im starting slow. full report and 5 computers and have been getting weird results. I then sorted the report by computer name and seems to be a bit better.
    is there a trick you should do?

    boy this report takes time to run when it updates so i have it set to manual calculate

  7. #7
    Registered User
    Join Date
    05-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2007 / Excel 2013
    Posts
    6

    Re: How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply n

    this is odd.. at line 489.. it all changes to no.. no matter what i try.. up to this the entire sheet works.. but after line 489.. no way..
    is there a forumula limit?
    office 2007???

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply n

    nope cant see why it wouldn't work after 489
    mind you i dont know how big your sheet is
    B$1:$B$10000 change all those ranges to the smallest needed
    what does your actual formula say in 489

  9. #9
    Registered User
    Join Date
    05-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2007 / Excel 2013
    Posts
    6

    Re: How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply n

    Quote Originally Posted by martindwilson View Post
    nope cant see why it wouldn't work after 489
    mind you i dont know how big your sheet is
    B$1:$B$10000 change all those ranges to the smallest needed
    what does your actual formula say in 489

    i didnt actually notice it was set to 10,000 as i thought it was 100,000 so that was my issue!!!!

    so now i have my report done my way.. very slow way..

    so now im going to try and convert it over to how yours is... only downside i see here is that it still takes 10 minutes roughly to update!!! and that is not with all added yet...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1