Automatic transposition depending on occurence & Incidence of value per month

[jimwillshire]

Apologies "shg"

Dear All,

This is my first post, please be gentle with me!

I have a worksheet which is extracted from another piece of software (Interherd) & I have some data....

What I'd like to do is paste the data into a worksheet, then have the data copied (automatically) onto the next worksheet, but in the order I would like it, specifically:
One of the datasets comes as comma delimited values (in the same cell)
e.g. BL,BR
I would like the BL to appear in a new row (into a newly created row so that the data below isn't disrupted) & also the associated info to be transferred
e.g. the date associated with the BL

I would then like to know how many times BL appears per month
The date values are entered as 28/3/08, therefore between the 1/3/08-31/3/08 how many times does BL appear?

I hope this makes sense, I've attached a pdf to demonstrate the data (couldn't get the Excel file attached)

Thanks for any help in advance

Jim

[shg]

Welcome back, Jim

Excel files need to be zipped and less than 100K. Can you attach a workbook that shows both the data as imported, and the desired results?

[jimwillshire]

Thanks for sorting that one for me, please find the attached .zip with the enclosed Excel file detailing what the data looks like & how I would LIKE it to look!

Thanks again "SHG"

Jim

[shg]

If you want it in just that format, it will take a few lines of code. I'll try to look at it leter this evening if no one else sorts it out before then.

[jimwillshire]

Thanks SHG, look forward to it

[shg]

Jim, I think this does what you want for the first part.

Sub jw()
    Dim iRow    As Long
    Dim i       As Long
    Dim astr()  As String
    
    iRow = 2
    Do
        astr = Split(Cells(iRow, "C"), "/")
        If UBound(astr) > 0 Then
            Cells(iRow, "C").Value = astr(0)
            For i = 1 To UBound(astr)
                Rows(iRow).Copy
                Rows(iRow + 1).Insert
                Cells(iRow + 1, "C").Value = astr(i)
                iRow = iRow + 1
            Next i
        End If
        iRow = iRow + 1
    Loop Until IsEmpty(Cells(iRow, "A"))
End Sub