Please bear with me and read through all this if you can help.
I have a large list of old Product Serial Numbers which I need to check against a new large list of existing Product Serial Numbers and need to locate Numbers not duplicated, from both lists. The two lists are in separate work sheets, or could be made to be part of the same (when cut and pasted- not important immediately).
I have got a formula which would normally do that, but there is a deeper problem.
The serial numbers are 11 digits long and some have leading zeros. I know I can format it using "CUSTOM" and using 11 zeros. The problem arises when the new Product Serial Numbers are imported or cut & pasted. They seem to be formatted to CATEGORY - "Special", TYPE - "Numer PESEL" LOCALE - "Polish". Whatever that format is.
I've tried to reformat it to CUSTOM, or GENERAL or NUMBER with no decimal points, but it ALWAYS reverts back to the original format.
I've tried to use the LEFT formula command to reduce the data to 10 digits. Although this allows the format NOT to revert back to "Numer PESEL", it doesn't help as those products without leading zeros will get looked at the same as those with a different leading digit.
Unfortunately I have found another problem to one of my many "solutions".
The new list is formatted in "numer PESEL" and the old list is formatted as "text". I've tried to format both lists as "Number - no decimal places" (which kills any leading zeros). This I thought would work, but the cells that were "Text" formatted remain an 11 digit number with leading zeros. The only way that this then becomes right is when you double click an individual cell and ENTER of that cell. It does become what looks like a number without leading zeros. The problem is that I now have to do that same "double click - Enter" to every cell. The list I have is more than 4000 items long and I can't possibly do that to all the cells. (I used t think F9 would update everything but that is only if under MENU : TOOLS-OPTIONS-CALCULATION is set to manual.
I know this is long winded but I would appreciate anyones patience and help.
rizvisa1
Posts4478Registration dateThursday January 28, 2010StatusContributorLast seenMay 5, 2022766 Jul 2, 2011 at 10:23 AM
Could you on some file sharing site, upload a sample with with sample data to show how the data is right now and how you would like it to be. include all macros etc that you have in the file Upload the file and post the link to the file back here
I'll try. I've never posted files on a file sharing site.
Any suggestions ? I'm keen to get to the bottom of this problem.
rizvisa1
Posts4478Registration dateThursday January 28, 2010StatusContributorLast seenMay 5, 2022766 Jul 3, 2011 at 07:58 AM
there are many. The moderators of this forum seem to have soft corner for speedyshare.com. I like docs.google but there are others too. I would have given you few more sites but for reasons beyond comprehension of this mortal, Gods of this forum a.k.a moderators don't seem to like any thing but speedyshare and their auto filter would delete the message.
Jul 2, 2011 at 10:26 AM
Any suggestions ? I'm keen to get to the bottom of this problem.
Jul 3, 2011 at 07:58 AM
Jul 5, 2011 at 09:31 AM
I hope you find it and note that I did reply.
Thanks