Formula IF or Variable Formula

[skynet1984]

hello all!!!

this is my first post here! i hope someone can help me

am working a project in excel for a cabin allocation.

basically i have finish it already but the only thing is missing its an IF or variable formula that gives me if a female or male are sharing the same cabin and if yes to show it as COUPLE

in Excel i have all my employees list with cabin number and gender.

is there any formula i can use to tell excel that if she find a Male & Female in the same cabin it should give me "Couple" status?

thank you all for your help

luca

[oeldere]

Maybe like this.

Please Login or Register  to view this content.

[BB1972]

skynet1984,

This can be easily done. Can you post a copy/sample of your workbook please?


EDIT: You may not need to - oeldere got there before me with the same logic as my own.

[skynet1984]

thank you!!! this was very fast!

i have attached my excel file.

as you can see am using different tabs
first one is the reports of availability and second my employee list with cabin and ***

please let me know what do you think about that

thank you

[oeldere]

Maybe like this.

See column G.

[Ace_XL]

Try this..

=IF(AND(COUNTIF(D:D,D2)=2,SUMPRODUCT(--($D$2:$D$763=D2),--($F$2:$F$763=F2))=1),"Couple","No Couple")

[skynet1984]

dear Oeldere

This is perfect
but are you able to reply the same formulas on the first excel tab column "M"?

it should capture the information on 2nd tab "crew list"

do you think can be done?

thank alot for your help really appreciate it!

luca

[Ace_XL]

In Cell M3 of "cabin master" sheet use

Please Login or Register  to view this content.

and drag down

[skynet1984]

Hello ace!

thank you! i pasted in M3 and its just beautiful
but when i try to drag it down to capture other records it wont

looks like dragin the formula down is not working.... any idea why?

thank you so much!

[skynet1984]

actually i found out why

i posted a screen shot... very wierd....how can i remove the little green corner flag?

thanks again for your suppost

luca

[Ace_XL]

Column 'D' of the 'crew list' tab has cells input as text rather than numbers, hence the anomaly.

Convert this column to numbers by doing the following..
-In a blank cell type 1
-Copy this cell
-Select your range of data (Column D)
-Paste special --multiply

[skynet1984]

it doesnt like the .jpg file?

[oeldere]

See in the file from #5 in column I of the sheet crew list for the solution of your problem.

[skynet1984]

woah it worked! amazing!

one last thing and i should be set

if i copy and past the cabin from anther how can i make sure the cells remain set as number?

thank you!

[oeldere]

after changed it from text in value

use

copy => paste special => values.

[skynet1984]

ehmm for some reasons doesnt work.

from my main program i can export the cabin list and it set in "text number

so everytime i update my crew list with the new cabin even if i past as value it still placing it as "text number

get frustrated lol

am sure there must be a way to set the set for any number pasted as "text to be just a number cell

[oeldere]

and i found some VBA code on this forum to do that for you.

See the attached file.

Choose cell D1 and run the macro.

I'm no macro expert and noticed it don't stop by itself, but maybe it is usefull to you.

Otherwise there are other members to perfect the code.

Please Login or Register  to view this content.

[oeldere]

Please Login or Register  to view this content.

You update it from another program?

I noticed it happens ofter if you import data from another program into excell.

[skynet1984]

Yes we have another program with all employee information and it keep changing everyday

so the main thing was to export in excel file and place the new update version of the list on the master cabin.

but the program its exporting the cabin column as "text so everytime i past it, it remain as "text

[oeldere]

then try this code in your files.

1 code for column D

and

1 code for column B

[skynet1984]

Dear oeldere

thank you so much for your help! everything is working just fine

now i decided to add more information on my sheet, i tried few formula but they are not giving me the result am looking for.

can you please check my screen shots and my new file?
do you think it can be done in excel using formulas?

thank you mate! really appreciate that!
this file will save me a lot of time cuz now am doing everything manually

[oeldere]

@ Luca Sacchetta

2 questions

What do you already tried yourself?

Please show in the file, in a few lines, the wanted result.

2 comments:
1) You have an password on your VBA => so forummembers can't add code in you file

2) you have to (could) use the solutions you been offering, and keep the solution in your (next) files.

in the new added file, the crew list column G is gone.