Can some one help to write a piece of vba code

[msh250]

I am new to VBA and would need help to write a piece of code for the requirements below:
i have a spreadsheet with 1000's of data

From the data in the column “N”, for each line:

• Copy-paste all the objects starting with “L-“ into a new column named “Location Type”
• Copy-paste all the objects starting with “T-Special “ into a new column named “Special Requirement”
• Copy-paste all the objects starting with “S-Echo” and the object “S-Zoom capable” into a new column named “Online Delivery”
• Copy-paste all the remaining objects into a new column named “Location attributes”

 

[bebo021999]

For fast help, could you attach a mini sheet via XL2BB tool?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[msh250]

For fast help, could you attach a mini sheet via XL2BB tool?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Book1
	M	N
1	Scheduled Finish as end time	Name
2	11:00	L-Analysis
3	15:00	L-CTA teach
4	15:00	L-CTA Analysis
5	15:00	L-MTV Teach
6	15:00	AM-OW;L-CTA Teach
7	13:00	S-EchoWLV;L-CTA Analysis
8	13:00	S-EchoWLV;L-CTA Teach
9	13:00	S-EchoWLV;L-CTA Teaching
10	13:00	L-CTA Analysis;AV-LOWER
11	13:00	L-CTA Analysis;AV-LOWER
12	13:00	L-CTA Analysis;AV-LOWER
13	13:00	L-CTA Analysis;AV-LOWER
14	13:00	L-CTA Analysis;AV-LOWER
15	16:00	L-CTA Teach;S-Moveable;S-Alter
16	12:00	S-EchoWLV;S-Echo Live;L-CTA Analysis
17	12:00	S-EchoWLV;S-Echo Live;L-CTA Analysis
18	12:00	S-EchoWLV;S-Echo Live;L-CTA Analysis
19	12:00	S-EchoWLV;S-Echo Live;L-CTA Analysis
20	18:00	@Zoom
21	18:00	@Zoom
22	18:00	@Zoom
23	16:00	L-Analysis
24	16:00	L-Analysis
25	16:00	L-Analysis
26	12:00	AM-OW;L-CTA Teach
27	12:00	AM-OW;L-CTA Teach
28	12:00	AM-OW;L-CTA Teach
29	12:00	AM-OW;L-CTA Teach
30	15:30	S-Zoom room -
31	12:00	AV-MEDIUM
32	10:00	L-Analysis
33	10:00	L-Analysis
34	14:00	AM-OW;L-CTA Teach
35	14:00	AM-OW;L-CTA Teach
36	14:00	AM-OW;L-CTA Teach
37	10:00	S-EchoWLV;S-Echo Live;L-CTA Teach;T-Special Medium
38	10:00	S-EchoWLV;S-Echo Live;L-CTA Teach;T-Special Medium
39	10:00	S-EchoWLV;S-Echo Live;L-CTA Teach;T-Special Medium
Sheet1

 

[bebo021999]

try again.
For some cells has multi values those belong to same column, like this: S-EchoXXX;S-EchoYYY, they will be put in same column, separated by semicolon .

Option Explicit
Sub Distribute()
Dim lr&, i&, j&, rng, arr(), s, st1 As String, st2 As String, st3 As String, st4 As String
lr = Cells(Rows.Count, "N").End(xlUp).Row
rng = Range("N2:N" & lr).Value ' assume data starts from N2
Range("O1:R1").Value = Array("Location Type", "Special Requirement", "Online Delivery", "Location attributes")
ReDim arr(1 To lr - 1, 1 To 4)
For i = 1 To lr - 1
    st1 = "": st2 = "": st3 = "": st4 = ""
    s = Split(rng(i, 1), ";")
    For j = 0 To UBound(s)
        If UCase(s(j)) Like "L-*" Then
            st1 = st1 & ";" & s(j): arr(i, 1) = Right(st1, Len(st1) - 1)
        ElseIf UCase(s(j)) Like "T-SPECIAL*" Then
            st2 = st2 & ";" & s(j): arr(i, 2) = Right(st2, Len(st2) - 1)
        ElseIf UCase(s(j)) Like "S-ECHO*" Or UCase(s(j)) Like "S-ZOOM*" Then
            st3 = st3 & ";" & s(j): arr(i, 3) = Right(st3, Len(st3) - 1)
        Else
            st4 = st4 & ";" & s(j): arr(i, 4) = Right(st4, Len(st4) - 1)
        End If
    Next
Next
Range("O2").Resize(UBound(arr), 4).Value = arr
End Sub