PDA

View Full Version : Solved: Select Cases using two columns



sdownes1
09-25-2008, 08:37 AM
I am a novice and I am trying to use VBA to compare two columns (cell by cell) and place a number in the third column. I am transferring a large number of employees (approx 6000) between companies and need to select the new benefits packages the employees are to recieve in the new company.

I have tried IF statements and Select Case, to no avail. I have attached a file with a sample of data.

Written out, what I am attempting to do is: (see attached file)

IF Column F = ESI AND Column G = EQN THEN column H would be "1"

I believed that this was an easy Select Case but I can't get it to work when comparing TWO columns and placing a number in a third column!:banghead:

Any advice to get me "jump started" on writing a select case statement would be greatly appreciated!

Bob Phillips
09-25-2008, 08:58 AM
Use a formula

=IF(AND(F2="ESI",G2="EQN"),1,"")

benny
09-25-2008, 08:59 AM
Hi sdownes1,

Try :
Formula in H2.

IF(AND(F2="ESI",G2="EQN"),1,"")

and copy down

sdownes1
09-25-2008, 09:13 AM
Thanks very much!
But I have multiple combinations, for example, F2 = "ENOI" and G2 = "EQL" which would = 3 in H1, would I need to nest all the variations in the formula or is there an other way

Bob Phillips
09-25-2008, 09:45 AM
best way would be to build a table with all combinations, say

ESIEQN 1
ENOIEQL 3
etc, in say K & L and use

=IF(ISNA(VLOOKUP(F2&G2,$K$1:$L$10,2,FALSE)),"",VLOOKUP(F2&G2,$K$1:$L$10,2,FALSE))

There is a space that gets injected into the second false, remove that.

sdownes1
09-25-2008, 10:01 AM
XLD - That is great! I appreciate the assistance! I have been banging my head against the wall for weeks! I truly appreciate it!