Listing the date, activity and name on a rota

[hurcuma]

Hi

We prepare a rota where volunteers assist with activities at Church, we normally just give out the rota (an excel sheet), but a number of people volunteer for more than one thing.

What I would like to do is to to give them a printed list showing in chronological order the activity they are assigned to. (thinking if I can achieve a listing, I can do a mail merge from excel into word so that I can include some additional text.

The mail merge part I am fine with.

I am struggling with how to take the table below, and transform it to the example I have called output below, it will obviously need to repeat moving on the next volunteer in the table.


Assuming Headings is Row 1 with columns A through to G

Date	Deacon	Vestibule	Church am	Front door	Projection Sunday	Car Park
07-Jan	M Bond	V Bond	R Pedro	G Pedro	R Townsend	S Everton
14-Jan	R Cole	B Bond	C Chaplin	J L-Rogers/J Patel	R Townsend	T Thompson
21-Jan	N Smith	B&B Smith	M Roberts	L Musk	M Murray	S Musk
28-Jan	A Hill	A Rogers	M Bond	N Thompson	M Murray	N Smith
04-Feb	S Everton	B L-Rogers	J Harper	S Musk	K Townsend	W Brown
11-Feb	M Bond	C Smith	H Thompson	T Thompson	R Townsend	L Musk
18-Feb	R Cole	M Gallagher	V Rogers	T Lamont	R Townsend	M Bond
25-Feb	N Smith	A Bashar	R Matthews	T Matthews	M Murray	T Lamont
Output
Date	Name	Activity
07-Jan	M Bond	Deacon
28-Jan	M Bond	Church AM
11-Feb	M Bond	Deacon
18-Feb	M Bond	Car Park

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Would any one be able to help me out on this as I am stumped on where to start. Many thanks

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[iggydarsa]

I"d be amazed if somebody can come up with a formula based solution.
If you are comfortable with it I can create a macro.

 

[hurcuma]

Hi Iggydarsa, I thought a marco would be required. My formulas are normally very strong, but macros out of my comfort zone, I'd very much appreciate your help.

 

[hurcuma]

The actual rota is a lot larger, and I can adapt the macro to the correct range.

 

[iggydarsa]

Ok here are the assumptions:
1- your first table is under the sheet called "Input"
2- your second table which macro will create, will post on a sheet named "Output"
3- Row 1 of each sheet has headers
4- The name you are searching is typed on the cell [I1]

Remember, this code might take a while to finish if you have thousands of rows.

Sub Transfer()
    'Clear Output sheet
    Sheets("Output").Range("A2:C" & Rows.Count).ClearContents
    rowOutput = 2
    
    'Find lastrow
    lrInput = Sheets("Input").Range("A" & Rows.Count).End(xlUp).Row
    
    'Search for name in Range I1
    nm = Sheets("Input").Range("I1").Value
    
    For r = 2 To lrInput 'loop through the rows
        For c = 2 To 7 'loop through columns B through G
            If Sheets("Input").Cells(r, c).Value = nm Then 'if name matches then transfer to Output
                Sheets("Output").Range("A" & rowOutput).Value = Sheets("Input").Range("A" & r).Value 'Date
                Sheets("Output").Range("B" & rowOutput).Value = nm 'Name
                Sheets("Output").Range("C" & rowOutput).Value = Sheets("Input").Cells(1, c).Value 'Activity
                rowOutput = rowOutput + 1
            End If
        Next c
    Next r
End Sub

 

[hurcuma]

Hi Iggydarsa, wow this is amazing, does the task perfectly.

Thank you so much, you have saved me hours of work.

 

[iggydarsa]

I'm glad it worked.
Enjoy.

 

[hurcuma]

Hi Iggydarsa

Thank you your help, I looked at your macro to see what I could learn and made a few amends, such as only putting the name in once on the output sheet and also applied it to my actual live spreadsheet. It was great to learning opportunity.

I wonder if you could help a bit more and save further admin time.

Here is the revised code
Sub Transfer()
'Clear Output sheet
Sheets("Output").Range("A3:B" & Rows.Count).ClearContents
rowOutput = 3

'Find lastrow
lrInput = Sheets("2018").Range("A" & Rows.Count).End(xlUp).Row

'Search for name in Range I1
nm = Sheets("2018").Range("A60").Value

For r = 2 To lrInput 'loop through the rows
For c = 2 To 28 'loop through columns B through AB
If Sheets("2018").Cells(r, c).Value = nm Then 'if name matches then transfer to Output
Sheets("Output").Range("B1").Value = nm 'Name
Sheets("Output").Range("A" & rowOutput).Value = Sheets("2018").Range("A" & r).Value 'Date
Sheets("Output").Range("B" & rowOutput).Value = Sheets("2018").Cells(1, c).Value 'Activity


rowOutput = rowOutput + 1
End If
Next c
Next r
End Sub

I have a list of people partaking in the rota in another excel sheet, if I pasted them from cell A60 on the 2018 worksheet, is it possible for the macro to start on A60, then generate the output, print the output, and then loop back to A61 to pick up and search for the next person dates/activites, print, repeat etc and then stop once it reaches a blank cell?

Regards

Drew

 

[iggydarsa]

If I understood your question correctly, only thing you have to change is this line
For r = 2 To lrInput
To
For r = 60 To lrInput

Does this info help?

 

[hurcuma]

Hi Iggydarsa

I'm not sure if it does, so on the sheet 2018, from cell A60, a61, a62, a63 etc I would have a list of all of those that partake in the rota.

A60 V Bond
A61 R Pedro
A62 G Pedro
A63 R Townsend
A64 S Everton
A65 R Cole
A66 B Bond
A67 C Chaplin
A68 L-Rogers/J Patel
ETC

I press the shape button which I have assigned the macro to, and then is look at the A60 (V Bond), goes of and produces the output in to the Output worksheet, then prints that worksheet, then goes back and searches for R Pedro, of to the output folder, print etc

In essence Search -> Print the output sheet -> repeat until there is blank cell eg. A69 in the example above.

Sorry if I didn't explain well.