Conditional formatting

D

deanl33069

Board Regular
Joined
May 2, 2019
Messages
120
HI all
I have several large numbers
019332961003232531020025441319041221179549530
19332961003232531020021401319041221179549570
1933296100323253102002236131904122117954954
0193329610032325310200235813190411211795387001
933296100323253102002280131904112117953858
01933296100323253102002122131904122117954955
01933296100323253102002324131904122117954961
019332961003232531020021261319041221179549390193
32961003232531020021361319041221179549450
1933296100323253102002136131904122117954956
01933296100323253102002126131904112117953898
019332961003232531020024001319042321179625220
1933296100323253102002230131904112117953904
01933296100323253102002410131904112117953871

that need to have conditional formatting applied however when i do this they all come up as duplicates when they are not.
any thoughts?>??
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi,

Tried to post your numbers with custom number format "0" and also as text, the conditional formatting for duplicates seemed to work in the same fashion. However, when posted as numbers, these were rounded up. I don't know how these numbers are to be used for any mathematical operations, if not then posting as text might work:

Screenshot:
open
open


https://drive.google.com/open?id=1DZhlGyOGDBGoSl6USiB83esfM34Q00_-

ABCD
Number (custom format "0")Text
19332961003232500000000000000000000000000000019332961003232531020025441319041221179549530
1933296100323250000000000000000000000000000019332961003232531020021401319041221179549570
19332961003232500000000000000000000000000001933296100323253102002236131904122117954954
1933296100323250000000000000000000000000000000193329610032325310200235813190411211795387001
933296100323253000000000000000000000000000933296100323253102002280131904112117953858
193329610032325000000000000000000000000000001933296100323253102002122131904122117954955
193329610032325000000000000000000000000000001933296100323253102002324131904122117954961
19332961003232500000000000000000000000000000000019332961003232531020021261319041221179549390193
3296100323253100000000000000000000000000032961003232531020021361319041221179549450
19332961003232500000000000000000000000000001933296100323253102002136131904122117954956
193329610032325000000000000000000000000000001933296100323253102002126131904112117953898
19332961003232500000000000000000000000000000019332961003232531020024001319042321179625220
19332961003232500000000000000000000000000001933296100323253102002230131904112117953904
193329610032325000000000000000000000000000001933296100323253102002410131904112117953871

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A1[/TH]
[TD="align: left"]Number (custom format "0")[/TD]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]Text[/TD]
[/TR]
[TR]
[TH]A2[/TH]
[TD="align: left"]1.93329610032325E+43[/TD]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]019332961003232531020025441319041221179549530[/TD]
[/TR]
[TR]
[TH]A3[/TH]
[TD="align: left"]1.93329610032325E+43[/TD]
[/TR]
[TR]
[TH]B3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]19332961003232531020021401319041221179549570[/TD]
[/TR]
[TR]
[TH]A4[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]1933296100323253102002236131904122117954954[/TD]
[/TR]
[TR]
[TH]A5[/TH]
[TD="align: left"]1.93329610032325E+44[/TD]
[/TR]
[TR]
[TH]B5[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C5[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]0193329610032325310200235813190411211795387001[/TD]
[/TR]
[TR]
[TH]A6[/TH]
[TD="align: left"]9.33296100323253E+41[/TD]
[/TR]
[TR]
[TH]B6[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D6[/TH]
[TD="align: left"]933296100323253102002280131904112117953858[/TD]
[/TR]
[TR]
[TH]A7[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D7[/TH]
[TD="align: left"]01933296100323253102002122131904122117954955[/TD]
[/TR]
[TR]
[TH]A8[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D8[/TH]
[TD="align: left"]01933296100323253102002324131904122117954961[/TD]
[/TR]
[TR]
[TH]A9[/TH]
[TD="align: left"]1.93329610032325E+46[/TD]
[/TR]
[TR]
[TH]B9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D9[/TH]
[TD="align: left"]019332961003232531020021261319041221179549390193[/TD]
[/TR]
[TR]
[TH]A10[/TH]
[TD="align: left"]3.2961003232531E+40[/TD]
[/TR]
[TR]
[TH]B10[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C10[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D10[/TH]
[TD="align: left"]32961003232531020021361319041221179549450[/TD]
[/TR]
[TR]
[TH]A11[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B11[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C11[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D11[/TH]
[TD="align: left"]1933296100323253102002136131904122117954956[/TD]
[/TR]
[TR]
[TH]A12[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B12[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C12[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D12[/TH]
[TD="align: left"]01933296100323253102002126131904112117953898[/TD]
[/TR]
[TR]
[TH]A13[/TH]
[TD="align: left"]1.93329610032325E+43[/TD]
[/TR]
[TR]
[TH]B13[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C13[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D13[/TH]
[TD="align: left"]019332961003232531020024001319042321179625220[/TD]
[/TR]
[TR]
[TH]A14[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B14[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C14[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D14[/TH]
[TD="align: left"]1933296100323253102002230131904112117953904[/TD]
[/TR]
[TR]
[TH]A15[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B15[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C15[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D15[/TH]
[TD="align: left"]01933296100323253102002410131904112117953871[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
ty
no matter how i format the cell they all come back as duplicates .......
i formatted the cells first and after but it didnt work.
SAD...
 
Upvote 0
deanl33069 said:
ty
no matter how i format the cell they all come back as duplicates .......
i formatted the cells first and after but it didnt work.
SAD...
Click to expand...


Other way can be that use sort on the columnand then use a formula for conditional formatting =A1=A2.
 
Upvote 0
Apparently if you only have numbers valid up to 42 digits. But if in a column auxiliary to the number you add a letter at the end and in the auxiliary column you apply the duplicate format, then it works.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:422.02px;" /><col style="width:389.7px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; text-align:right; ">019332961003232531020025441319041221179549530</td><td >019332961003232531020025441319041221179549530a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; text-align:right; ">19332961003232531020021401319041221179549570</td><td >19332961003232531020021401319041221179549570a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#92d050; text-align:right; ">1933296100323253102002236131904122117954954</td><td >1933296100323253102002236131904122117954954a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#d0d7e5; text-align:right; ">0193329610032325310200235813190411211795387001</td><td >0193329610032325310200235813190411211795387001a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#d0d7e5; text-align:right; ">933296100323253102002280131904112117953858</td><td >933296100323253102002280131904112117953858a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#92d050; text-align:right; ">01933296100323253102002122131904122117954955</td><td >01933296100323253102002122131904122117954955a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#92d050; text-align:right; ">01933296100323253102002324131904122117954961</td><td >01933296100323253102002324131904122117954961a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#d0d7e5; text-align:right; ">019332961003232531020021261319041221179549390193</td><td >019332961003232531020021261319041221179549390193a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#d0d7e5; text-align:right; ">32961003232531020021361319041221179549450</td><td >32961003232531020021361319041221179549450a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#92d050; text-align:right; ">1933296100323253102002136131904122117954956</td><td >1933296100323253102002136131904122117954956a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#92d050; text-align:right; ">01933296100323253102002126131904112117953898</td><td >01933296100323253102002126131904112117953898a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffc7ce; text-align:right; ">01933296100323253102002410131904112117953871</td><td style="background-color:#ffc7ce; ">01933296100323253102002410131904112117953871a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#92d050; text-align:right; ">1933296100323253102002230131904112117953904</td><td >1933296100323253102002230131904112117953904a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffc7ce; text-align:right; ">01933296100323253102002410131904112117953871</td><td style="background-color:#ffc7ce; ">01933296100323253102002410131904112117953871a</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=A1&"a"</td></tr></table></td></tr></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
One-off conditional formatting duplicate exeptions
Replies
1
Views
484
Artik
Artik
J
  • Question Question
Conditional formatting based on value of another cell
Replies
3
Views
741
etaf
etaf
S
  • Question Question
Conditional Formatting - How to Properly Copy + Paste without Duplicating Rules
Replies
1
Views
3K
6StringJazzer
6StringJazzer
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
278
Madam_Xavier
M
S
  • Question Question
VBA To Filter Conditionally Formatted coloured cells in multiple columns
Replies
1
Views
717
bobsan42
bobsan42

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top