Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Duplicate Values
I have a 120 x 120 matrix like so:
50220 12200 40220 51211 60300 11120 50230 12210 40220 51211 60302 11122 50231 12220 40221 51220 60305 11130 50232 12220 40224 51220 60305 11130 53100 15010 41000 51221 60320 11212 53101 15010 41000 51231 60322 11222 53202 15020 41004 51310 60401 11230 53202 15020 41020 51310 60421 11230 53220 15100 41204 51311 60422 11230 53221 15100 41220 51330 60425 11232 54100 15110 42001 52111 61000 12110 54131 15110 42020 52121 61002 12130 ....and so forth. Is there anyway to highlight all values that are duplicated? Some values have lots of duplicates; others have only one. I'm not looking to eliminate the duplicates, just being able to recognize them visually. Thanks yet again. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Duplicate Values
Use conditional formating. Suppose the first data cell is A1 and the table
range is A1:D4. Select the first cell, then Data Conditional Formatting. In the first drop down, select 'Formula Is'. In the text box, enter the formula =COUNTIF($A$1:$D$4,A1)1 (adjust the range to match your table's range) Then click on Format and choose a format that will produce the highlight you want. Click OK. Then select A1 and Edit Copy. Select the entire table and Edit Paste Special, select Formats and click OK. "Rothman" wrote: I have a 120 x 120 matrix like so: 50220 12200 40220 51211 60300 11120 50230 12210 40220 51211 60302 11122 50231 12220 40221 51220 60305 11130 50232 12220 40224 51220 60305 11130 53100 15010 41000 51221 60320 11212 53101 15010 41000 51231 60322 11222 53202 15020 41004 51310 60401 11230 53202 15020 41020 51310 60421 11230 53220 15100 41204 51311 60422 11230 53221 15100 41220 51330 60425 11232 54100 15110 42001 52111 61000 12110 54131 15110 42020 52121 61002 12130 ...and so forth. Is there anyway to highlight all values that are duplicated? Some values have lots of duplicates; others have only one. I'm not looking to eliminate the duplicates, just being able to recognize them visually. Thanks yet again. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting Duplicate Values
Thank you. I hate it when an obvious solution doesn't appear obvious until
after I someone else shows it to me. "bpeltzer" wrote: Use conditional formating. Suppose the first data cell is A1 and the table range is A1:D4. Select the first cell, then Data Conditional Formatting. In the first drop down, select 'Formula Is'. In the text box, enter the formula =COUNTIF($A$1:$D$4,A1)1 (adjust the range to match your table's range) Then click on Format and choose a format that will produce the highlight you want. Click OK. Then select A1 and Edit Copy. Select the entire table and Edit Paste Special, select Formats and click OK. "Rothman" wrote: I have a 120 x 120 matrix like so: 50220 12200 40220 51211 60300 11120 50230 12210 40220 51211 60302 11122 50231 12220 40221 51220 60305 11130 50232 12220 40224 51220 60305 11130 53100 15010 41000 51221 60320 11212 53101 15010 41000 51231 60322 11222 53202 15020 41004 51310 60401 11230 53202 15020 41020 51310 60421 11230 53220 15100 41204 51311 60422 11230 53221 15100 41220 51330 60425 11232 54100 15110 42001 52111 61000 12110 54131 15110 42020 52121 61002 12130 ...and so forth. Is there anyway to highlight all values that are duplicated? Some values have lots of duplicates; others have only one. I'm not looking to eliminate the duplicates, just being able to recognize them visually. Thanks yet again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH() and duplicate values -- is there a workaround? | Excel Worksheet Functions | |||
Is there a way MATCH() can cope with duplicate values? | Excel Worksheet Functions | |||
highlighting duplicate values | Excel Worksheet Functions | |||
DUPLICATE VALUES | Excel Worksheet Functions | |||
Duplicate values | Excel Discussion (Misc queries) |