Formula too long - Shorter version?

  • Thread starter Thread starter Blade2304
  • Start date Start date
B

Blade2304

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
 
You're a genius, i have no idea what that does but it works exactly ho
i want, thankyo
 
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.
 
Back
Top