Data Validation - If Statement Problem

[Ardiko]

Hi,

I saw a video showing how to do data validation with an if statement in an old excel. I wish to do this now for Excel 2010.

I also need an IF statement for 3 three headings 'A' 'B' 'C' that will throw out duplicate records. e.g. From the example below chair would be thrown out as it has the same year and number for the record. Same for Wardrobe.

A B C
YEAR LIST OF ITEMS NUMBER
2007 CHAIR 1
2007 CHAIR 1
2008 TABLE 1
2009 WARDROBE 2
2009 KETTLE 2
2009 FISHTANK 2
2009 WARDROBE 2

[dilipandey]

Hi Ardiko,

Welcome to the forum.

Try using below formula:-

Please Login or Register  to view this content.

See attached:- validating data.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[Ardiko]

Hi DILIPandey,

Thank you for your solution - Do you know if I would be able to pop this into the 'Data validation' program in Excel 2010? As I need it to produce a list of errors to go through rather than filtering a particular column with the undesireable cells.

*Also, when the duplicate is marked twice, is it possible to only mark the first time it appears. So in the working example only one chair would be noted.

Regards,
Ardiko

[dilipandey]

yes.. so do you want to implement this as a "Data validation" in Excel 2010 where no duplicates can be entered by a user ?
do you want to restrict the user or just a warning when he /she enters duplicates ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[Ardiko]

I want it for data validation purposes, so if im stacking data together if one of the lines has the same three variables for example like the above example it will mark it for deleting but only one of them.

As the data is being reviewed not created, it has to be implemented after.

*If it doesn't work the first method will suffice if it only marks one of the duplicates, perhaps 'Double Record' for the first entry and then 'Delete for the duplication.

[dilipandey]

Okay..I that case you can append (stack) the data and just drag the conditional formatting to down.. that's it. duplicates will be highlighted and you can remove them easily.

If you want you can copy the conditional formatting formula to a new column towards right side and there you'll have true and false as outputs.. all trues are duplicates and you can filter and easily remove them

Regards,
DILIPandey

<click on below 'star' if this helps>

[Ardiko]

Thank you for your time DILIPandey! +rep

[dilipandey]

you are welcome Ardiko.. Cheers :

Regards,
DILIPandey

<click on below 'star' if this helps>