Transposing List Problem

[pop]

I have a 2 column list of mixed items which include date and details of activity

Date Activity
1st January 2015 Activity
2nd January 2015 Activity 1,Activity 2, Activity 3
3rd January 2015 Activity
4th January 2015 Activity
4th January 2015 Activity 4, Activity 5,Activity 6, Activity 7


a) there are single items describing single activity for a date and

b) multiple items of activity for a date

I would like to be able use a a formula to transpose the multiple items of activity so that they become separate single items adhering to the same format ie date and a single activity rather than multiple activities while maintaining the list.

Date Activity
1st January 2015 Activity
2nd January 2015 Activity 1
2nd January 2015 Activity 2
2nd January 2015 Activity 3
3rd January 2015 Activity
4th January 2015 Activity
4th January 2015 Activity 4
4th January 2015 Activity 5
4th January 2015 Activity 6
4th January 2015 Activity 7


Any Help greatly appreciated

 

[Nebu]

Hi:

This can be achieved through a basic macro , here is the coding

Sub SplitActivity()

Application.ScreenUpdating = False

Dim arr As Variant
Dim i As Long, j As Long, cnt As Long, k As Long

i = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
k = i + 6

For j = 2 To i
    On Error Resume Next
    arr = Split(Cells(j, 2).Value, ",")
    For cnt = LBound(arr) To UBound(arr)
        Sheet1.Cells(k, 2) = arr(cnt)
        Sheet1.Cells(k, 1) = Sheet1.Cells(j, 1)
        k = k + 1
    Next
    Next

Application.ScreenUpdating = True

End Sub

Click on the split button.

Thanks

 

[pop]

Hi Nebu,

Thank you very much for your quick response. The worked.

Could you tell me how the code can be changed so that

(a) the new list overwrites the old list
(b) the new list is written to a new sheet
(c) the new list is written next to the old list (with one column gap)

Thanking you in advance

 

[Nebu]

Hi:

Find the attached, the data will be populated in sheet1 now. This will over write your old data in sheet1 whenever you run the macro. I did not get your point number 3.


Thanks

 

[pop]

Thanks for the amendment.

Sorry about the confusion for point 3. I was seeking to write the new list next to the old list starting in column D where Column A = "Date", Column B="Activity" Column C is a GAP so that Column D would be the transposed "Date" Column E the transposed "Activity" column. I hope this makes it a bit clearer. Thanks you for you assistance in advance .

 

[Nebu]

Hi:

Use this code.

Sub SplitActivity()

Application.ScreenUpdating = False

Dim arr As Variant
Dim i As Long, j As Long, cnt As Long, k As Long

i = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
k = 2

For j = 2 To i
    On Error Resume Next
    arr = Split(Cells(j, 2).Value, ",")
    For cnt = LBound(arr) To UBound(arr)
        Sheet1.Cells(k, 5) = arr(cnt)
        Sheet1.Cells(k, 4) = Sheet1.Cells(j, 1)
        k = k + 1
    Next
    Next

Application.ScreenUpdating = True

End Sub

Thanks

 

[pop]

Thank you very much for you help. All the macros worked.