Formula too long - Shorter version?
 

I am trying to put a check into a worksheet that look down a list of
concatenated items and if a duplicate appears it tells me, if not it
says ok. As entries are made into the datasheet it needs to be checked
against the rest of the items in the list to see if it has a duplicate.
There is a possibility 100 concatenated items in the list. I figured
out i can do this with if statements within if statements, this works
but the formula gets too long and eventually isnt allowed. I need to
shorten the formula but dont know how. This is a working formula i am
using

=IF(AG5=AG4,"Duplicate",IF(AG5=AG6,"Duplicate",IF( AG5=AG7,"Duplicate","OK")))

Basically each time an entry is made into the sheet the entries are
concatenated in a column, the sheet then needs to check with all
concatenated items in that list above and below it, but not itself. If
a its a duplicate this would be returned in the cell beside the item, if
its not its ok, for every entry that is made. Clear as mud i know.

Please help


--
Blade2304
------------------------------------------------------------------------
Blade2304's Profile: http://www.excelforum.com/member.php...o&userid=33492
View this thread: http://www.excelforum.com/showthread...hreadid=532914

Formula too long - Shorter version?
 

Try

=IF(COUNTIF(AG$4:A$G100,AG5)1,"Duplicate","OK")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=532914

Formula too long - Shorter version?
 

You're a genius, i have no idea what that does but it works exactly how
i want, thankyou


--
Blade2304
------------------------------------------------------------------------
Blade2304's Profile: http://www.excelforum.com/member.php...o&userid=33492
View this thread: http://www.excelforum.com/showthread...hreadid=532914

Formula too long - Shorter version?
 

I notice I put one of the dollar signs in the wrong place but

COUNTIF(AG$4:AG$100,AG5)

counts the number of occurrences of AG5 within the range AG4:AG100. In
this case because AG5 is part of the range the answer must be at least
1, however if the result is more than 1 that means you have a
duplicate.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=532914