compare columns

[Guest]

I have close codes in columns for two tied accounts. CLosed codes 1 and 9 =
active, and 2, 3, 8 = closed. I need to compare the two columns to
determine which accounts have one active account and one closed account.
Example: If Column A = 1 or 9 and Column C = 2, 3 or 8, I need to indicate
in Column D "inequal". If Column A = 1 or 9 and Column C = 1 or 9 I need
Column D to indicate "equal". If Column A = 2, 3, or 8 and Column C = 1 or
9, I need Column D to indicate "inequal" and if Column A = 2, 3, or 8 and
Column C = 2, 3, or 8 I need Column D to indicate "equal".

 

[Guest]

Perhaps something like this:

=IF(MOD(AVERAGE(CHOOSE((A1:B1)+1,0.1,1,10,10,0.1,0.1,0.1,0.1,10,1)),1)=0,"Equal","Inequal")

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Bernard Liengme]

Try this
=IF(OR(A1=1,A1=9),IF(OR(C1=1,C1=9),"Equal",IF(OR(C1=2,C1=3,C1=8),"Unequal","?")),IF(OR(A1=2,A1=3,A1=8),IF(OR(C1=2,C1=3,C1=8),"Equal",IF(OR(C1=1,C1=9),"Unequal","??")),"???"))

If A has 1 or 9 but C does not have valid number (1,9,2,3,8) then you get
one question mark (?)
If both A has 2, 3 or 8 but C is invalid you get (??)
If C is 1 or 9 and A is invalid you get 3 ?
If A is invalid you get (???)

Experiment to double check.
best wishes

 

[Guest]

Hello Fluffy,

Someone here probably has an easier formula but here goes:

=IF(OR(AND(OR(A1=1,A1=9),OR(C1=2,C1=3,C1=8)),AND(OR(A1=2,A1=3,A1=8),OR(C1=1,C1=9))),"Inequal","Equal")

Judith