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/

Your verbosity is excused. Actually, it’s a good thing.
Yes, what you want is very possible. You’ll have to do a bit of magi
with string manipulation to separate the information you need, but tha
shouldn’t be too difficult. I’ll work on some examples of what you’l
need, but in the meantime, what do you want done with the information?
Once we figure out the age of the product, how would you like tha
information stored? We could give each serial number it’s own row an
have Model Number in column 1, Serial Number in column 2 and th
manufactured Date in column 3. How’s that? Could you take it fro
there? - Piku

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

Yeah, I could take it from there. Thanks for your help.

Corbi

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

Hi Corbin

someone might have a neater solution but this seems to extract (on my tests
anyway) the month & year from the string AS LONG AS "S/N " exists in the
cell

=VLOOKUP(IF(ISNUMBER(VALUE(MID(A2,FIND("S/N",A2,1)+5,1))),
MID(A2,FIND("S/N",A2,1)+6,1),
MID(A2,FIND("S/N",A2,1)+5,1)),
{"A",1;"B",2;"C",3;"D",4;"E",5;"F",6;"G",7;"H",8;" I",9;"J",10;"K",11;"L",12}
,2,FALSE)
& "/" &
IF(ISNUMBER(VALUE(MID(A2,FIND("S/N",A2,1)+5,1))),
MID(A2,FIND("S/N",A2,1)+4,2),
199 & MID(A2,FIND("S/N",A2,1)+4,1))

Cheers
Julie
(yep it all needs to go in one cell!)

"Corbin " wrote in message
...
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/

I did some shorthand here. I just hard coded the example you gave int
what I wrote. I figured I’d let you figure out how to pass in th
various cells’ values. Also I didn’t include any way for it to loo
through each of the cells you have, my recommendation, given how thi
works is to have it take the values from one sheet and send it’
results to a new sheet entirely, thus avoiding the need for it to kee
inserting rows to accommodate each SN. This code, as it is, will pu
the model number into the first column once for each serial number an
each serial number will go into the second column followed by the yea
in the third column and the letter that indicates the month in th
fourth. I figured you could convert the months… Let me know if yo
need any further assistance or explanation. I’ll be happy to do so
though I personally learn more for having to sort out all that stuf
myself… - Pikus

infoStr = "00B1233455 S/N 04A34565, 04B234565, 01F32456, 9G12345"
infoStrLen = Len(infoStr)
snCount = 0
For x = 1 To infoStrLen
If Mid(infoStr, x, 1) = " " Then
snCount = snCount + 1
End If
Next x
snCount = snCount - 1
ReDim snArray(snCount) As String
For x = snCount - 1 To 0 Step -1
snStart = InStrRev(infoStr, " ")
snStart = snStart + 1
snArray(x) = Mid(infoStr, snStart)
infoStr = Left(infoStr, snStart - 3)
Next x
snStart = InStrRev(infoStr, " ")
infoStr = Left(infoStr, snStart - 1)
For x = 1 To snCount
Cells(x, 1).Value = infoStr
Cells(x, 2).Value = snArray(x - 1)
If IsNumeric(Mid(snArray(x - 1), 3, 1)) Then
Cells(x, 3).Value = 1990 + Left(snArray(x - 1), 1)
Cells(x, 4).Value = Mid(snArray(x - 1), 2, 1)
Else
Cells(x, 3).Value = 2000 + Left(snArray(x - 1), 2)
Cells(x, 4).Value = Mid(snArray(x - 1), 3, 1)
End I

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

Thanks for all your help. I'm certain that with your code I can figur
out the rest of the easy bits. Thanks again.

Corbi

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

On Fri, 4 Jun 2004 08:54:14 -0500, Corbin
wrote:

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.


Perhaps the following VBA code will help. It assumes the Warranty Period is
stated in Months. It also requires a later version of Excel/VBA as it uses the
Split function.

It assumes a layout similar to what you posted; and the only check for validity
is that "S/N" must appear.

InWarrantyCount should return the number of strings (after S/N) that are within
the warranty period.

OutWarrantyCount does the obvious.

==============================
Option Explicit
Public Infostr As Variant
Function InWarrantyCount(WarrantyPeriod, Info) As Integer
Dim i As Integer
Dim dt1 As Date

Infostr = Split(Info, ", ")

If InStr(1, Infostr(0), "S/N") = 0 Then
MsgBox ("No S/N")
Exit Function
End If

Infostr(0) = Mid(Infostr(0), InStr(1, Infostr(0), "S/N") + 4, 255)

For i = 0 To UBound(Infostr)
dt1 = ConvertDate(Infostr(i))
If DateDiff("m", dt1, Date) <= WarrantyPeriod Then
InWarrantyCount = InWarrantyCount + 1
End If
Next i

End Function

Function OutWarrantyCount(WarrantyPeriod, Info) As Integer
OutWarrantyCount = InWarrantyCount(WarrantyPeriod, Info)
OutWarrantyCount = UBound(Infostr) + 1 - OutWarrantyCount
End Function

Private Function ConvertDate(dt) As Date
Dim yr As Integer, month As Integer
Dim MonthPos As Integer
Const day As Integer = 1

If IsNumeric(Left(dt, 2)) Then
yr = 2000 + Left(dt, 2)
MonthPos = 3
Else
yr = 1990 + Left(dt, 1)
MonthPos = 2
End If

month = Asc(Mid(dt, MonthPos, 1)) - 64

ConvertDate = DateSerial(yr, month, day)

End Function
=====================


--ron

On Fri, 04 Jun 2004 16:23:13 -0400, Ron Rosenfeld
wrote:

I missed something in your first posting. The code below compares the warranty
period with today's date. You'll need to pass that parameter to the function
and also change the DATE parameter Date Returned. So try the following
modifications on the lines not preceded by a "greater than" sign ().

==============================
Option Explicit
Public Infostr As Variant

Function InWarrantyCount(DateReturned, WarrantyPeriod, Info) As Integer

Dim i As Integer
Dim dt1 As Date

Infostr = Split(Info, ", ")

If InStr(1, Infostr(0), "S/N") = 0 Then
MsgBox ("No S/N")
Exit Function
End If

Infostr(0) = Mid(Infostr(0), InStr(1, Infostr(0), "S/N") + 4, 255)

For i = 0 To UBound(Infostr)
dt1 = ConvertDate(Infostr(i))
If DateDiff("m", dt1, DateReturned) <= WarrantyPeriod Then
InWarrantyCount = InWarrantyCount + 1
End If
Next i

End Function


Function OutWarrantyCount(DateReturned, WarrantyPeriod, Info) As Integer

OutWarrantyCount = InWarrantyCount(WarrantyPeriod, Info)
OutWarrantyCount = UBound(Infostr) + 1 - OutWarrantyCount
End Function

Private Function ConvertDate(dt) As Date
Dim yr As Integer, month As Integer
Dim MonthPos As Integer
Const day As Integer = 1

If IsNumeric(Left(dt, 2)) Then
yr = 2000 + Left(dt, 2)
MonthPos = 3
Else
yr = 1990 + Left(dt, 1)
MonthPos = 2
End If

month = Asc(Mid(dt, MonthPos, 1)) - 64

ConvertDate = DateSerial(yr, month, day)

End Function
=====================


--ron

--ron