Flag Duplicate Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a nested logic function to identify or flag duplicate entries in a spread sheet. The basic logic is as follows
If A2=A1 AND B2=B1 AND D2=D1 then return "Duplicate" in E2, I would then use a condition formt to highlight E2 in Red

I have been trying nested if functions but I am getting nowhere fast

The solution is probably sitting in front of me but I can't for the life of me see it

Any help is appreciated
 
Hi

Try using this as a conditional format in E2:
Formula is . . . =AND(A2=A1,B2=B1,D2=D1)
and set the cell pattern to red.

Andy.

GrahamJ said:
I am trying to create a nested logic function to identify or flag
Click to expand...
duplicate entries in a spread sheet. The basic logic is as follows:
If A2=A1 AND B2=B1 AND D2=D1 then return "Duplicate" in E2, I would then
Click to expand...
use a condition formt to highlight E2 in Red.
 
If you only want to look at 3 rows, you can use your idea like

IF(AND(A2=A1,B2=B1,D2=D1),"Duplicate","No duplicate")

but that's not the standard way of looking through lists for duplicates (you
might try COUNTIF and IF together to compare lists).

check out http://www.cpearson.com/excel/duplicat.htm



GrahamJ said:
I am trying to create a nested logic function to identify or flag
Click to expand...
duplicate entries in a spread sheet. The basic logic is as follows:
If A2=A1 AND B2=B1 AND D2=D1 then return "Duplicate" in E2, I would then
Click to expand...
use a condition formt to highlight E2 in Red.
 
Hi

Select a cell in row 1 (let's it be on sheet Sheet1) and create 3 named
ranges (Insert.Name.Define)
ColA==OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A1),1)
ColB==OFFSET(Sheet1!$B$1,,,ROW(Sheet1!$B1),1)
ColD==OFFSET(Sheet1!$D$1,,,ROW(Sheet1!$D1),1)

Select the range A1:D1, and then from menu Format.ConditionalFormatting
Select 'Formula Is' and enter the formula:
=AND((SUMPRODUCT((ColA=$A1)*(ColB=$B1)*(ColD=$D1))>1),AND(ColA<>"",ColB<>"",
ColD<>""))
Set the color for duplicate row. OK

Copy range A1:D1, and then PasteSpecial.Formats to range, you want
conditional formatting to be applied.
It's done!


--
(Don't use my reply address - it's spam-trap)

Arvi Laanemets


GrahamJ said:
I am trying to create a nested logic function to identify or flag
Click to expand...
duplicate entries in a spread sheet. The basic logic is as follows:
If A2=A1 AND B2=B1 AND D2=D1 then return "Duplicate" in E2, I would then
Click to expand...
use a condition formt to highlight E2 in Red.
 
Many thanks to both Andy B. and Dave R.

Your solutions worked like a charm

Thnaks, Agai

Graham
 
Back
Top