Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |