difficult formula help

[drobbins3006]

sheet 1 contains
column a = list of numbers
column b may or may not be populated
column c = one of these values: Empty, ThisList, CreditList, or Unique.

sheet 2 contains
column a = list of numbers

if b1 is unpopulated c1= "Empty" regardless of what is in a1

if a1 is duplicated in column a, and b1 is populated, c1 = "ThisList"

if a1 is found in sheet 2 column a, and b1 is populated, c1 = "CreditList"

if a1 is unique in column a, not found in sheet 2 column a, and b1 is populated, c1 = "Unique"

I have tried to figure out a formula, but will use any solution available.

Thanks to anyone who is willing to help.

 

[hyat]

Could be a problem with whole columns not being allowed by Excel 2002. Maybe you could try:

=IF(LEN(B1)>0,IF(COUNTIF($A$1:$A$1000,A1)>1,"ThisList",IF(COUNTIF(Sheet2!$A$1:$A$1000,A1)>=1,"CreditList","Unique")),"Empty")

Change 1000 to however many rows you like.

 

[drobbins3006]

Works great. Thank you very much.

If possible, I would like for the first or lasr occurance in sheet 1 column a to not show as "ThisList".

You have been very helpfull and this will serve my purpose until/if you can help.

 

[hyat]

Hey there. This may work:

=IF(LEN(B1)>0,IF(AND(COUNTIF($A$1:$A$1000,A1)>1,NOT(OR(MATCH(A1,$A$1:$A$1000,0)=ROW(),MAX(ROW($A$1:$A$1000)*($A$1:$A$1000=A1))=ROW()))),"ThisList",IF(COUNTIF(Sheet2!$A$1:$A$1000,A1)>=1,"CreditList","Unique")),"Empty")

It'll have to be entered using Ctrl+Shift+Enter, instead of just Enter. Then it can be copied and pasted down the remaining cells of Column C.

It might be more convenient to give a name to your Column A range. For example, you could name the range $A$1:$A$1000 as "MyRange". Then your formula would become:

=IF(LEN(B1)>0,IF(AND(COUNTIF(MyRange,A1)>1,NOT(OR(MATCH(A1,MyRange,0)=ROW(),MAX(ROW(MyRange)*(MyRange=A1))=ROW()))),"ThisList",IF(COUNTIF(Sheet2!$A$1:$A$1000,A1)>=1,"CreditList","Unique")),"Empty")

Again, this would be entered using Ctrl+Shift+Enter.

 

[drobbins3006]

Thank you very much. It will be a few days before I can check it out. I will post back when done.