Hello all.

First, let me thank you in advance for your help.

Ok. I have a messy list that I need to extract certain things from.
One cell in the list has both a model number and several serial numbers
(don't ask me why they put all this info into one cell).

Example: "00B1233455 S/N 04A34565, 04B234565, 01F32456, 9G12345"

The serial numbers (after the S/N) have a date code integrated into
their format that tells when it was manufactured. The first two digits
of the serial number are the year code, i.e. 04A34565 = year 04 or
2004. Just to make things more difficult, there are also one letter
year codes as in the last serial number of the above example. If there
is only one digit "X" before the letter then the year of manufacture is
199X. The letter after the year code indicates the month, i.e. A=Jan,
B=Feb, C=Mar etc.

My task is to examine each serial number (disregarding the model number
as it can sometimes be very similar to the serial numbers) and then
count whether or not the serial number falls within a certain warranty
period. For example, if the warranty period were one year, all the
serial numbers under one year old would be counted and then that number
inserted into another column called "Warranty" or something, and then
all the remaining serial numbers over one year old would be counted and
entered into a "Non-Warranty" column. I need to determine their age
down to the month. There is already a date column for when they were
returned so I would need to compare the extracted age to the month and
year of the date they were input.

I currently have VB code pulling this data in from a database and
ideally I would like to include code to manipulate this data in the
above way. I have somewhat limited coding experience and this kind of
manipulation of cells is beyond my ability. Tell me if this is even
worth trying.

I've thought about automatically seperating all the model numbers and
serial numbers and putting them each into their own cell to make them
easier to analyze but this would create different length rows which
would make summing harder. I've also thought about simply duplicating
each row for each serial number so that when a record comes in with one
model number and 4 serial numbers I would end up with 4 records each
with only one model number and one serial number. It doesn't really
matter what the end result of the recordset looks like because nobody
will be looking at that. Only graphs based on the numbers. Anyway, Im
sure you guys are much smarter and can think of better ways. Thanks
for your help and excuse my verbosity.

Corbin


---
Message posted from http://www.ExcelForum.com/