Index (Maybe)

[Chris26]

Hi

I have the following data in SHEET1

Column A -- Column B (Incident Dates)
Cluster 1 -- 1/5/2011, 5/5/2011, 9/5/2011
Cluster 2 -- , 5/5/2011, 8/5/2011
Cluster 3 --, 5/5/2011, 8/5/2011, 9/5/2011
----
Cluster 99 --


I would like to return the following results in SHEET2
Column A -- Column B -- Column C -- Column D etc
30/4/2011
1/5/2011 -- Cluster 1
2/5/2011
3/5/2011
4/5/2011
5/5/2011 -- Cluster 1 -- Cluster 2 -- Cluster 3
6/5/2011
7/5/2011
8/5/2011 -- Cluster 2 -- Cluster 3
9/5/2011 -- Cluster 1 -- Cluster 3
10/5/2011
etc

I am using Excel2007

Hope this makes sense.
All help appreciated.
Many Thanks in advance
Chris

 

[Yahya]

to do it
remove the first comma in in your date , like here

Excel Workbook
	A	B
2	Cluster 1	1/5/2011, 5/5/2011, 9/5/2011
3	Cluster 2	5/5/2011, 8/5/2011
4	Cluster 3	5/5/2011, 8/5/2011, 9/5/2011
5	Cluster 4	15/5/2011,20/5/2011,1/5/2011
6	Cluster 5
7	Cluster 6
8	Cluster 7
9	Cluster 8
10	Cluster 9
11	Cluster 10
12	Cluster 11
13	Cluster 12
14	Cluster 13
15	Cluster 14
16	Cluster 15
17	Cluster 16
18	Cluster 17
19	Cluster 18
20	Cluster 19
Sheet1

Excel 2007

then try this code

Sub Test()
Dim ArrD() As String, c As Range, LR As Long, Cl As Range, LC As Integer
LR = Sheets("sheet1").Range("b" & Rows.Count).End(xlUp).Row
For Each c In Sheets("sheet1").Range("B2:B" & LR)
    ArrD = Split(c.Value, ",")
    For i = LBound(ArrD) To UBound(ArrD)
        For Each Cl In Sheets("sheet2").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
            If CDate(Trim(ArrD(i))) = Cl.Value Then
                LC = Cells(Cl.Row, Columns.Count).End(xlToLeft).Column
                Cl.Offset(, LC).Value = c.Offset(, -1).Value
            End If
        Next Cl
    Next i
Next c
End Sub