Excel table results

QuestionEdit

I have a table (roster) in its simplest form, there are four columns and four rows. Date,Action1,Action2,Action3 Mon, ABC, DEF, GTue, MNO, ABC, DEF Wed, G MNO, XYZ

What I am after is a macro to produce the following results:

ABC Action1,Mon Action2,Tue

DEF Action2,Mon Action3,Tue

GAction3,Mon Action1,Wed

MNO Action1,Tue Action2,Wed

XYZ Action3,Wed

The idea is that once this roster is prepared, we can produce a new sheet and give each rostered person a list of all the dates, and actions that they are required to perform without them having to view the entire roster.

Is there a function or macro that can assist here? (Note: My table will continually be 8-10 columns, and 60-100 rows)

AnswerEdit

Hi Dan! How are you? Please extend the following code to all the tasks.

Sub TASKS() Dim r As Range, FIRST, TS As String Set r = Range("A1:D4") FIRST = "ABC" TS = "" For i = 2 To r.Rows.Count

J = 2: enc = False
  Do While enc = False And J <= r.Columns.Count

     If r.Cells(i, J) = FIRST Then
       enc = True
       Else
       J = J + 1
     End If
  Loop
  If enc = True Then
     TS = TS + r.Cells(i, 1) + ".Action " + LTrim(Str(J)) + Chr(13)
 End If

Next i MsgBox TS End Sub

Adelaide