Data Format Incosistency

[prasaddn]

Hi,


We have around 70 different workbooks, from where we consolidate data of more than 250 records. All these sheets have claim numbers like 26112516038300 (just example - 14 digits), but never starts with 0.


for some unforeseen reasons the format these numers some times getting changed from general, text or custom format (00000000000000), these gets interchanged .


Even after I select the entire column and reset to one format and then use it for my vlookup to find the status or just to even check if claim is repeated in next days work, the look up fails.


Any suggestions on data format issue?


Regards,

Prasad

 

[Hui]

Prasad

Depending on the use of the numbers in your systems It may be worth forcing the numbers to be Text or vise versa with a vba script that resets them every time the workbook is opened or closed


Also if you have that many files have you thought of moving the data to Access or SQL and putting it all in one file.