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)
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
Advertisement