# Solved: Excel 15 digit limitation



## jayeliot (Feb 3, 2003)

Excel 2003

I have researched the Excel's 15 digit limitation in this forum as well as others. I know that a workaround would be to change the column to text format. But I am hoping you guys might have another work around.

I am scanning in bar-codes that are 30 digits in length. We have a problem in that some of the documents being scanned contain duplicate bar-codes and I am trying to identify them. Using Conditional Format of =COUNTIF(A:A,A1)>1 the color of the cell will change to Red if we encounter a duplicate. I copied this format to the entire column A.

During my testing at my desk, when I enter a 10, 12 or 15 characters, this conditional format will identify the duplicates. The column is set as a number field. Once I start scanning the 30 digit bar-codes I had to change the fields to be text due to the 15 digit limitaion. But once I did that I started getting inaccurate results.

Cells containing *non-duplicates *suddenly changed color to Red..... not always, just occassionally enough so that we have no confidence that we are finding the duplicates. In a column of 113 scanned bar-codes, 5 cells changed to red yet none of these 5 were duplicates. Any idea why this would happen ; any work arounds that you can suggest ?

Thanks

Jay


----------



## slurpee55 (Oct 20, 2004)

What exactly is the command in your conditional formatting?


----------



## jayeliot (Feb 3, 2003)

In cell A1, I went to Format / Conditional Format.

When the dialogue box opens I selected "formula is" and then entered this formula

=COUNTIF(A:A,A1)>1. 

I then copied this format and pasted it to all cells in column A. When I scan my bar-codes, all entries go into cells in column A. 

The color change to red is all part of the conditional formatting.


----------



## slurpee55 (Oct 20, 2004)

Hmmm, the only way I can get an error is if the number has preceding zeros - Excel reads 001 just like 1.


----------



## jayeliot (Feb 3, 2003)

Leading zeroes is not part of the bar-code, just whole numbers between 1 and 9

Is there any way to get Excel to recognize a 30 digit number without it imposing a decimal point at the 15th digit ? I saw somewhere that there is a free download but it seemed like a bit of a nuisance as they constantly make you acknowledge that you are using the free version.


----------



## slurpee55 (Oct 20, 2004)

Could you post some of the data you have that is causing these errors? I cannot duplicate it except in the way I mentioned earlier, using leading zeros.


----------



## OBP (Mar 8, 2005)

Have you considered using Access for this application?
You can easily prevent duplication or if required find it.


----------



## jayeliot (Feb 3, 2003)

I do not have Access on my PC but I will ask management. In the meantime I found a work around.

In column A we will enter our 30 digit data, all cells in A will be defined as Text.
In column B I have entered a formula =LEFT(A1,15) . This will show the first 15 digits
In column C I have entered a formula =RIGHT(A1,15) . This will show the last 15 digits

I have copied these formulas down columns B and C. Since 15 digits no longer violates the Excel Limitations, I now apply the conditional format listed above to both columns B and C.

If two cells turn RED in column B but does not turn RED in column C, we do not have a real duplicate. Only when the cells in B *AND *C turn RED do we have a true duplicate.


----------



## jayeliot (Feb 3, 2003)

Here is some data. Within these 110 or so bar-codes, there are no duplicates. My conditional format should have shown no duplicates, but 5 cells turned red as though there were dup's. 

Just for the record, I did change at least two digits in each of these entries for privacy concerns so when you work with them, you may not get the same 5 erroneous dup's that I got.


420463689102861175425112107682
420500039102861175425112122327
420500359102861175425112122693
420501429102861175425112112068
420502639102861175425112131852
420502669102861175425112132935
420503179102861175425112129938
420504699102861175425112118662
420511069102861175425112119475
420521419102861175425112262679
420522059102861175425112137468
420522459102861175425112104342
420531329102861175425112108591
420532159102861175425112175256
420532199102861175425112112748
420535119102861175425112121965
420535329102861175425112108904
420535759102861175425112108812
420537169102861175425112108111
420537199102861175425112108219
420539419102861175425112115343
420540219102861175425112111825
420543119102861175425112113875
420546269102861175425112060391
420546619102861175425112061403
420547299102861175425112112763
420549149102861175425112061329
420551089102861175425112112243
420551299102861175425112111819
420553119102861175425112106460
420553439102861175425112110533
420553759102861175425112108910
420560019102861175425112113861
420560119102861175425112117487
420570709102861175425112106685
420571109102861175425112061991
420581029102861175425112117278
420586019102861175425112061557
420586019102861175425112108193
420586019102861175425112115015
420592309102861175425112111007
420592549102861175425112111011
420596029102861175425112108767
420600109102861175425112106742
420600169102861175425112109492
420600469102861175425112108182
420600539102861175425112061453
420600619102861175425112061437
420600629102861175425112108057
420601019102861175425112108427
420601209102861175425112061387
420601209102861175425112113403
420601239102861175425112113922
420601309102861175425112061363
420601539102861175425112112622
420604179102861175425112108279
420604199102861175425112115633
420604239102861175425112112909
420604269102861175425112108302
420604639102861175425112061939
420605649102861175425112107034
420606069102861175425112061797
420606099102861175425112112489
420606139102861175425112106392
420606399102861175425112060311
420606459102861175425112111621
420606519102861175425112111543
420606559102861175425112111571
420610199102861175425112062654
420610619102861175425112110071
420611019102861175425112109176
420611149102861175425112108163
420657819102861175425112106913
420660649102861175425112114323
420669019102861175425112111551
420673579102861175425112113653
420678809102861175425112109196
420684509102861175425112113317
420685029102861175425112106657
420685219102861175425112110558
420686409102861175425112059998
420687529102861175425112121891
420690019102861175425112061911
420800129102861175425112109552
420802129102861175425112109662
420808649102861175425112106933
420813219102861175425112114314
420826019102861175425112111873
420834409102861175425112109813
420834559102861175425112062213
420836619102861175425112113103
420837049102861175425112113103
420837099102861175425112061683
420837139102861175425112107044
420840109102861175425112113385
420840659102861175425112113396
420840849102861175425112062617
420841199102861175425112111418
420843409102861175425112060029
420874019102861175425112115451
420874109102861175425112060152
420881019102861175425112113833


----------



## Zack Barresse (Jul 25, 2004)

A workaround should be ok for the 30-digit length, checking 15 digits from both sides...


```
=SUMPRODUCT((LEFT($A$2:A2,15)=LEFT(A2,15))*(RIGHT($A$2:A2,15)=RIGHT(A2,15)))
```
This will take place of your formula and/or conditional format. Having too much data may make this recalc very slow though, as it is somewhat of an 'expensive' calculating formula.

HTH


----------



## jayeliot (Feb 3, 2003)

That is quite a formula and it seems to work. I looked it up in Excel Help and it is way over my head. 

When I pasted this in column B, adjacent to my data in column A, any duplicate returns a result of "2", a triplicate returns a value of "3", while the original returns a value of "1". Any non-duplicates return a value of "1" also.

I added something to your proposal. In column B I added another conditional format. If the value of the cell is greater than "1", change the cell color to Red. This helps to highlight the duplicate with a nice bright color.

In my work around, there is one advantage. Using the conditional format, I highlight not only the duplicate(s) but the original(s) as well. This offers us some advantage in locating both.

If you can suggest a way to find the original as well as the duplicates using your work around, that would be great.

Thanks


----------



## slurpee55 (Oct 20, 2004)

I found 5 duplicates in your list also - fascinating! What I did was then put this in the next column
=RIGHT(A49,9)
which just reads the last 9 numbers. No duplicates unless I entered them, using your Countif formula on the 9 numbers.


----------



## slurpee55 (Oct 20, 2004)

Zack, I put your formula into the conditional formatting of Column A (with the addition that the sum >1), and it works great but it also highlights all the blank cells...


----------



## Zack Barresse (Jul 25, 2004)

Yeah, you'd have to combat that. For a CF formula, maybe (if you selected the entire column, A1 being the activecell, assuming row 1 was for headers and not included)...


```
=(ROW()<>1)*(SUMPRODUCT((LEFT($A$2:A2,15)=LEFT(A2,15))*(RIGHT($A$2:A2,15)=RIGHT(A2,15)))>1)*($A1<>"")
```
HTH


----------



## OBP (Mar 8, 2005)

Should you have resource to use Access it does not find any Duplicates in your data, but should there be any it will list them seperately or delete them if required.


----------



## slurpee55 (Oct 20, 2004)

It also does not have the 15 digit limitation...but, hey, OBP, what's the fun in that?


----------



## DotHQ (May 6, 2008)

Sounds like you have a solution to your problem. I simply wanted to add that if your mgmt does not want to spring for the cost of Access, you could get er done with MySQL open source DB.


----------



## jayeliot (Feb 3, 2003)

Thanks so much for your help. We have tried the two suggested formulas and both of them work. I will mark this resolved.

Jay


----------

