| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Simple Formula?
Hi, I have a problem which I need someone to look at. I have a list of 11 teams and I need a formula to check the data and for it to identify an error message if more than two of the same teams are listed. e.g. Arsenal Aston Villa Liverpool Chelsea Arsenal Wigan West Brom Arsenal Arsenal Birmingham Liverpool I would need a cell to highlight the error that there are two many arsenal entries as only two are allowed Can anyone help? Regards Paul -- pauldaddyadams ------------------------------------------------------------------------ pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
|
#2
|
|||
|
|||
|
Hi,
Assume the entries are within A2:A20 e.g. B2=IF(COUNTIF($A$2:A2,A2)1,"Dup","Ok") and copy down to B20 Regards, A "pauldaddyadams" wrote: Hi, I have a problem which I need someone to look at. I have a list of 11 teams and I need a formula to check the data and for it to identify an error message if more than two of the same teams are listed. e.g. Arsenal Aston Villa Liverpool Chelsea Arsenal Wigan West Brom Arsenal Arsenal Birmingham Liverpool I would need a cell to highlight the error that there are two many arsenal entries as only two are allowed Can anyone help? Regards Paul -- pauldaddyadams ------------------------------------------------------------------------ pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
|
#3
|
|||
|
|||
|
Believe you have a typo?!?!
=IF(COUNTIF($A$2:A2,A2)2,"Dup","Ok") -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "aristotle" wrote in message ... Hi, Assume the entries are within A2:A20 e.g. B2=IF(COUNTIF($A$2:A2,A2)1,"Dup","Ok") and copy down to B20 Regards, A "pauldaddyadams" wrote: Hi, I have a problem which I need someone to look at. I have a list of 11 teams and I need a formula to check the data and for it to identify an error message if more than two of the same teams are listed. e.g. Arsenal Aston Villa Liverpool Chelsea Arsenal Wigan West Brom Arsenal Arsenal Birmingham Liverpool I would need a cell to highlight the error that there are two many arsenal entries as only two are allowed Can anyone help? Regards Paul -- pauldaddyadams ------------------------------------------------------------------------ pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
|
#4
|
|||
|
|||
|
Hmmm. Don't think that works, either
Maybe: =IF(COUNTIF($A$20:A2,A2)2,"Dup","Ok") "RagDyeR" wrote: Believe you have a typo?!?! =IF(COUNTIF($A$2:A2,A2)2,"Dup","Ok") -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "aristotle" wrote in message ... Hi, Assume the entries are within A2:A20 e.g. B2=IF(COUNTIF($A$2:A2,A2)1,"Dup","Ok") and copy down to B20 Regards, A "pauldaddyadams" wrote: Hi, I have a problem which I need someone to look at. I have a list of 11 teams and I need a formula to check the data and for it to identify an error message if more than two of the same teams are listed. e.g. Arsenal Aston Villa Liverpool Chelsea Arsenal Wigan West Brom Arsenal Arsenal Birmingham Liverpool I would need a cell to highlight the error that there are two many arsenal entries as only two are allowed Can anyone help? Regards Paul -- pauldaddyadams ------------------------------------------------------------------------ pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
|
#5
|
|||
|
|||
|
RD's suggested formula *=IF(COUNTIF($A$2:A2,A2)2,"Dup","Ok")* will work. The third time the same name is entered in Column A, the immediate cell to its right (in Column B) will show "Dup" ... Duke Carey Wrote: Hmmm. Don't think that works, either Maybe: =IF(COUNTIF($A$20:A2,A2)2,"Dup","Ok") "RagDyeR" wrote: Believe you have a typo?!?! =IF(COUNTIF($A$2:A2,A2)2,"Dup","Ok") -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "aristotle" wrote in message ... Hi, Assume the entries are within A2:A20 e.g. B2=IF(COUNTIF($A$2:A2,A2)1,"Dup","Ok") and copy down to B20 Regards, A "pauldaddyadams" wrote: Hi, I have a problem which I need someone to look at. I have a list of 11 teams and I need a formula to check the data and for it to identify an error message if more than two of the same teams are listed. e.g. Arsenal Aston Villa Liverpool Chelsea Arsenal Wigan West Brom Arsenal Arsenal Birmingham Liverpool I would need a cell to highlight the error that there are two many arsenal entries as only two are allowed Can anyone help? Regards Paul -- pauldaddyadams ------------------------------------------------------------------------ pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499 View this thread: http://www.excelforum.com/showthread...hreadid=394615 -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
|
#6
|
|||
|
|||
|
Will work...if you only want to mark the 3rd occurrence. People often want
to mark ALL entries that are dupes, to allow them to determine which to keep and which to purge... "BenjieLop" wrote: RD's suggested formula *=IF(COUNTIF($A$2:A2,A2)2,"Dup","Ok")* will work. The third time the same name is entered in Column A, the immediate cell to its right (in Column B) will show "Dup" ... Duke Carey Wrote: Hmmm. Don't think that works, either Maybe: =IF(COUNTIF($A$20:A2,A2)2,"Dup","Ok") "RagDyeR" wrote: Believe you have a typo?!?! =IF(COUNTIF($A$2:A2,A2)2,"Dup","Ok") -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "aristotle" wrote in message ... Hi, Assume the entries are within A2:A20 e.g. B2=IF(COUNTIF($A$2:A2,A2)1,"Dup","Ok") and copy down to B20 Regards, A "pauldaddyadams" wrote: Hi, I have a problem which I need someone to look at. I have a list of 11 teams and I need a formula to check the data and for it to identify an error message if more than two of the same teams are listed. e.g. Arsenal Aston Villa Liverpool Chelsea Arsenal Wigan West Brom Arsenal Arsenal Birmingham Liverpool I would need a cell to highlight the error that there are two many arsenal entries as only two are allowed Can anyone help? Regards Paul -- pauldaddyadams ------------------------------------------------------------------------ pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499 View this thread: http://www.excelforum.com/showthread...hreadid=394615 -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
|
#7
|
|||
|
|||
|
To highlight the 3rd entry of the same team: 1. Go to "Format/Conditional Formatting" 2. For Condition 1, choose "Formula Is" 3. In the formula box, enter =countif($A$2:A2,A2)2 4. Click on "Format" 5. Choose your highlighting color (Color box is somewhere in the middle) 6. Click "OK" 7. Click "OK" Hope this works for you. Regards. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| simple interest formula in excel | Excel Discussion (Misc queries) | |||
| Help - Simple Formula ! | Excel Discussion (Misc queries) | |||
| Simple formula help | Excel Worksheet Functions | |||
| help with simple formula | Excel Worksheet Functions | |||
| Excel Miscalculates simple formula..Help!! | Excel Worksheet Functions | |||
Hybrid Mode
