|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Hi Duke, I am not offering or commenting on a solution that *"people often want to mark ALL entries that are dupes."* What people often want is not what the OP was asking here. Paul was specifically looking for a solution to identify the third entry as there are *"only two allowed."* Regards. Duke Carey Wrote:[color=blue] 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: [color=green] 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:[color=green] 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 ---------------------------------------------------------------------------- -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
Appreciate you acting as my lawyer.<vbg
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "BenjieLop" wrote in message ...[color=blue] Hi Duke, I am not offering or commenting on a solution that *"people often want to mark ALL entries that are dupes."* What people often want is not what the OP was asking here. Paul was specifically looking for a solution to identify the third entry as there are *"only two allowed."* Regards. Duke Carey Wrote:[color=green] 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 -------------------------------------------------------------------------- --[color=darkred] -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=394615 |
| All times are GMT +1. The time now is 04:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com