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]

Maybe, in cell C1 and fill down:
=IF(LEN(B1)>0,IF(COUNTIF($A:$A,A1)>1,"ThisList",IF(COUNTIF(Sheet2!$A:$A,A1)>=1,"CreditList","Unique")),"Empty")

 

[drobbins3006]

"ThisList" and "Empty" work like they should, however "Unique" and CreditList do not.

Thanks for giving it a shot, if you think of something else please post it.

 

[hyat]

Can you post an example of the data in Sheet 1 Columns A and B and Sheet 2 Column A for the case where you're not getting the expected result?

 

[hyat]

Change the portion highlighted in red to the name of your sheet 2:

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

 

[drobbins3006]

hold on

 

[hyat]

Also, if that doesn't work, try enclosing the name in single quotes. Like this:

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

 

[drobbins3006]

is there a difference in how this would work between 2007 and 2002 versions? It seems to work fine on 2007 spreadsheet (home) but doesnt work in when I move it to 2002 version (work) spreadsheet.

 

[hyat]

Hmm, I'm not too sure actually. Is there anybody who can help on this?

 

[drobbins3006]

I appreciate your help. I will keep playing with it and let you know.