Multiple dates per row - to then index all dates with new rows based on the activity

[lisaspencer]

Hi - I apologise in advance if this is a confusing one. I'm probably overcomplicating it myself but if someone is able to help, that would be amazing!

I have a chart of data with multiple dates and they all correspond with one another and will appear and reappear based on certain rules. Example, from a teacher's POV with marking. So column A displays the student name, column B displays the assignment name, column C displays the date it was set, column D displays the date it is due and etc etc.

My end goal is to turn this data into a calendar. I've found a template online that can convert a 2 column set of data into a calendar with the date on column A, and the event on Column B. So I'm trying to find a way to index all the dates in the first set of data and create an 'event' for each of those dates based on what the event is for. Example. 1st Jan - Keiras Assignment 1 was set, 2nd Jan - Keira's Assignment 2 was set. 3rd - Keira's Assignment 1 was submitted, 4th Jan - Keira's Assignment 3 was set, 5th Jan - Keira's Assignment 1 needs to be marked etc. So basically for every new date, a new row is produced which can then go into this calendar.

In my head, I thought the activity could be set like =E7&" "&F7&" "&ColumnTitleSET or DUE or SUBMITTED - based on the date that it is set for in said column

I think I need to find a way to separate the data based on the event rather than just data per row
But yes, if someone can help me get to the right direction that would be great

 

[Legacy 143009]

Are you ok with VBA?
Also I would recommend to make a seperate column for status. It will make reporting easier in the future (ie making a Pivot Table)

In addition, is your sample data correct? "To be marked" date seems to be referring to 301?!

 

[Legacy 143009]

I think this should work:

Sub test()
  Dim myRange As Range, lRow As Long, i As Long
  lRow = Cells(Rows.Count, 4).End(xlUp).Row
  Set myRange = Range("I7:K" & lRow, "M2:M" & lRow)
  i = 7
  For Each Rng In myRange
    If Rng.Value <> "" Then
      Cells(i, 1).Value = Rng.Value
      Cells(i, 2).Value = Cells(Rng.Row, 5).Value & " " & Cells(Rng.Row, 6).Value
      Cells(i, 3).Value = Cells(6, Rng.Column).Value
      i = i + 1
    End If
  Next
End Sub

 

[lisaspencer]

this is amazing! thank you so much!

 

[Legacy 143009]

Gald it did work! Thanks for the feedback