Is anyone an Excel pro?

Bribebox

Bribebox

Regular Member
Joined
Aug 11, 2010
Messages
261
Reaction score
70
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:
Advertise on BHW
Bribebox said:
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
Click to expand...

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.
 
"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:
Bribebox said:
"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
Click to expand...
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
 
theseodude said:
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
Click to expand...
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:
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.
 
Bribebox said:
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
Click to expand...

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!
 
Last edited:
Advertise on BHW
You must log in or register to reply here.
Sign up now!

Main Menu

Home Main Forum List Black Hat SEO White Hat SEO BHW Newbie Guide Blogging Black Hat Tools Social Media Downloads

Marketplace

Account Selling Content / Copywriting EBooks / Methods Hosting Misc Proxies For Sale SEO - Link building SEO - Packages Social Media Social Media - Panels Web Design More

Making Money

Affiliate Programs Hire a Freelancer IM Journeys Making Money Pay Per Click Site Flipping Want to Buy

BlackHat World

Dispute Resolution Forum Suggestions & Feedback Introductions Lounge
Back
Top