Nesting a function to return nothing if source cell is blank (contains another formula)

[Jamuson]

I am trying to get this function to disregard blank cells as duplicates from the source column.

=IF(COUNTIF($N$5:$N5,$N5)>1,"DUPLICATE","")

I need to ignore the first instance of a value in column N and return "DUPLICATE" for all duplicates, but not "DUPLICATE" for blank cells.

I know this is probably an easy fix, I can't seem to nest the function to ignore blank cells.
Any help would be much appreciated.

Jayme

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	M	N
5		a
6
7	DUPLICATE	a
8
9		b
10	DUPLICATE	a
11
12	DUPLICATE	a
13
14	DUPLICATE	b
15	DUPLICATE	a
16
17		c
18	DUPLICATE	c
Sheet1065

Cell Formulas
Range	Formula
N6,N16,N11,N8	N6	=""
M5:M18	M5	=IF(SUMPRODUCT(($N$5:$N5=$N5)*($N$5:$N5<>""))>1,"DUPLICATE","")

 

[Peter_SSs]

I can't seem to nest the function to ignore blank cells.

You could nest your original function like this.

22 03 29.xlsm
	M	N
5		a
6
7	DUPLICATE	a
8
9		b
10	DUPLICATE	a
11
12	DUPLICATE	a
13
14	DUPLICATE	b
15	DUPLICATE	a
16
17		c
18	DUPLICATE	c
Dupes

Cell Formulas
Range	Formula
N6,N16,N11,N8	N6	=""
M5:M18	M5	=IF(N5="","",IF(COUNTIF($N$5:$N5,$N5)>1,"DUPLICATE",""))

 

[Jamuson]

Thank you both, both solutions worked like a charm!

 

[Peter_SSs]

You're welcome. Glad we could help.

 

[jtakw]

You're welcome.