1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Is anyone an Excel pro?

Discussion in 'BlackHat Lounge' started by Bribebox, Jan 4, 2013.

  1. Bribebox

    Bribebox Regular Member

    Joined:
    Aug 11, 2010
    Messages:
    253
    Likes Received:
    60
    Hey, everyone! :)
    Is anyone an MS Excel pro? I need one simple task done... It seems really simple, but when I try to do it in Excel, my head is not working properly... :confused:
    Here is the task:

    ....A ....B ....C....
    1 QQ . FF
    2
    3

    If A1 and B1 exists, copy to A2(QQ) and A3 (FF).
    IF A1 exist and B1 not, copy to A2(QQ) and leave A3 blank.
    IF B2 exist and A1 not, copy to A2(FF) and leave A3 blank.
    IF none of A1 and B1 exist, do nothing.

    It sounds simple, but I really don't know how to make it working... :D
     
    Last edited: Jan 4, 2013
  2. theseodude

    theseodude Regular Member

    Joined:
    Jun 25, 2012
    Messages:
    303
    Likes Received:
    88
    I dont get what you mean by "exists"..do you mean if it's filled with something, do this, if it's blank, do that?
    it would be something like =IF(A1="", "FF", "") so you put this formula in any cell, and if A1 is blank, that cell will be filled with FF, if A1 is not blank, then that cell will be blank.
     
    • Thanks Thanks x 1
  3. Bribebox

    Bribebox Regular Member

    Joined:
    Aug 11, 2010
    Messages:
    253
    Likes Received:
    60
    "Exists" means any numbers, words, letters... As you said, IF A1 is blank, that cell will be filled with FF, that's ok, but if A1 is not blank, then cell must be filled with QQ. That was easy part... Hard part: what if A1 and B1 exists together? I mean they have numbers, words and so on. How can I make them go like this: IF A1 has letters, copy to A2, if B1 has letters, copy to A3. I could make this for different cells, but if I would do that, I would have blank gaps between A column... It is so hard to explain... Sorry... :D
     
    Last edited: Jan 4, 2013
  4. theseodude

    theseodude Regular Member

    Joined:
    Jun 25, 2012
    Messages:
    303
    Likes Received:
    88
    I dont get what you mean. it's not hard to explain, just write what you want to do like this so I know what you mean.
    If A1 is filled and A2 is blank, then fill A3 with QQ
     
    • Thanks Thanks x 1
  5. Bribebox

    Bribebox Regular Member

    Joined:
    Aug 11, 2010
    Messages:
    253
    Likes Received:
    60
    If A1 is filled and B1 is blank, then fill A2 with QQ
    If A1 is blank and B1 is filled, then fill A2 with FF
    If A1 is filled and B1 is filled, then fill A2 with QQ and fill A3 with FF
    If A1 is blank and B1 is blank, then do nothing (leave A2 blank).
    EDIT: Ok I made this myself, but htere is the problem. If I have more than one row of data? How to avoid blank gaps?
     
    Last edited: Jan 4, 2013
  6. mobo57

    mobo57 Registered Member

    Joined:
    Oct 30, 2010
    Messages:
    61
    Likes Received:
    20
    It's simple, A2 and A3 are nested conditional statements. Are you going to copy this down or is it just for use as you've set it out? Would make a difference on how it's put together. Use an IF statement with a nested AND.
     
    • Thanks Thanks x 1
  7. Bribebox

    Bribebox Regular Member

    Joined:
    Aug 11, 2010
    Messages:
    253
    Likes Received:
    60
  8. Standard Toaster

    Standard Toaster Regular Member

    Joined:
    Aug 29, 2009
    Messages:
    335
    Likes Received:
    190
    • Thanks Thanks x 1
  9. SpecialOne

    SpecialOne Registered Member

    Joined:
    Jan 12, 2011
    Messages:
    65
    Likes Received:
    21
    I am still learning Excel so couldn't find better solution for you... I think you made one mistake (I bolded, underlined it and make it red). It should be B1 instead of B2 right? If yes, copy these two formulas in following cells:

    A2 cell:

    =IF(AND(NOT(ISBLANK($A$1)),NOT(ISBLANK($B$1))),"QQ", IF(AND(NOT(ISBLANK($A$1)),ISBLANK($B$1)),"QQ", IF(AND(ISBLANK($A$1),NOT(ISBLANK($B$1))),"FF", IF(AND($A$1="",$B$1=""),"","ERROR"))))

    A3 cell:

    =IF(AND(NOT(ISBLANK($A$1)),NOT(ISBLANK($B$1))),"FF", IF(AND(NOT(ISBLANK($A$2)),ISBLANK($B$2)),"", IF(AND(ISBLANK($A$2),NOT(ISBLANK($B$2))),"", IF(AND($A$2="",$B$2=""),"","ERROR"))))


    Really sorry I couldn't make one single formula because I don't know how to return multiple values in multiple cells if argument is TRUE!
     
    • Thanks Thanks x 1
    Last edited: Jan 4, 2013