Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Highlighting all duplicates in a row
I am trying to highlight duplicates in a row and am using the explanation:
Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format-Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1. Then, click the Format button and select the font or color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be formatted as you selected. For example, if "Able" occurs twice in Range1, both occurrences of "Able" will appear highlighted. I am changing "A5" to the first cell in the row and still nothing happens. what am I doing wrong? thanks Jim |
#2
|
|||
|
|||
Select the range as stated, and assuming that we are highlighting duplicates
in row 1 starting at a1, just use the formula =COUNTIF($1:$1,A1)1 this shows all the duplicates, first and subsequent times. If you just want to show subsequent times, use =COUNTIF($A1:A1,A1)1 I would use the latter -- HTH RP (remove nothere from the email address if mailing direct) "Jimv" wrote in message ... I am trying to highlight duplicates in a row and am using the explanation: Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format-Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1. Then, click the Format button and select the font or color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be formatted as you selected. For example, if "Able" occurs twice in Range1, both occurrences of "Able" will appear highlighted. I am changing "A5" to the first cell in the row and still nothing happens. what am I doing wrong? thanks Jim |
#3
|
|||
|
|||
Bob,
What if I am working in a colum? "Bob Phillips" wrote: Select the range as stated, and assuming that we are highlighting duplicates in row 1 starting at a1, just use the formula =COUNTIF($1:$1,A1)1 this shows all the duplicates, first and subsequent times. If you just want to show subsequent times, use =COUNTIF($A1:A1,A1)1 I would use the latter -- HTH RP (remove nothere from the email address if mailing direct) "Jimv" wrote in message ... I am trying to highlight duplicates in a row and am using the explanation: Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format-Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1. Then, click the Format button and select the font or color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be formatted as you selected. For example, if "Able" occurs twice in Range1, both occurrences of "Able" will appear highlighted. I am changing "A5" to the first cell in the row and still nothing happens. what am I doing wrong? thanks Jim |
#4
|
|||
|
|||
Then use
=COUNTIF($A$1:A1,A1)1 -- HTH RP (remove nothere from the email address if mailing direct) "Jimv" wrote in message ... Bob, What if I am working in a colum? "Bob Phillips" wrote: Select the range as stated, and assuming that we are highlighting duplicates in row 1 starting at a1, just use the formula =COUNTIF($1:$1,A1)1 this shows all the duplicates, first and subsequent times. If you just want to show subsequent times, use =COUNTIF($A1:A1,A1)1 I would use the latter -- HTH RP (remove nothere from the email address if mailing direct) "Jimv" wrote in message ... I am trying to highlight duplicates in a row and am using the explanation: Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format-Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1. Then, click the Format button and select the font or color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be formatted as you selected. For example, if "Able" occurs twice in Range1, both occurrences of "Able" will appear highlighted. I am changing "A5" to the first cell in the row and still nothing happens. what am I doing wrong? thanks Jim |
#5
|
|||
|
|||
Thanks Bob, that worked!
"Bob Phillips" wrote: Then use =COUNTIF($A$1:A1,A1)1 -- HTH RP (remove nothere from the email address if mailing direct) "Jimv" wrote in message ... Bob, What if I am working in a colum? "Bob Phillips" wrote: Select the range as stated, and assuming that we are highlighting duplicates in row 1 starting at a1, just use the formula =COUNTIF($1:$1,A1)1 this shows all the duplicates, first and subsequent times. If you just want to show subsequent times, use =COUNTIF($A1:A1,A1)1 I would use the latter -- HTH RP (remove nothere from the email address if mailing direct) "Jimv" wrote in message ... I am trying to highlight duplicates in a row and am using the explanation: Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format-Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1. Then, click the Format button and select the font or color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be formatted as you selected. For example, if "Able" occurs twice in Range1, both occurrences of "Able" will appear highlighted. I am changing "A5" to the first cell in the row and still nothing happens. what am I doing wrong? thanks Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Duplicates | Excel Worksheet Functions | |||
Finding and Deleting duplicates in a column | Excel Worksheet Functions | |||
Finding Duplicates and somehow flagging them in another column | Excel Discussion (Misc queries) | |||
removing duplicates testing in 2 coloms | Excel Worksheet Functions | |||
Find duplicates | Excel Discussion (Misc queries) |