Listing the date, activity and name on a rota

hurcuma

New Member
Joined
Oct 19, 2017
Messages
12
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
DateDeaconVestibuleChurch amFront door Projection
Sunday
Car Park
07-JanM BondV BondR PedroG PedroR TownsendS Everton
14-JanR ColeB BondC ChaplinJ L-Rogers/J PatelR TownsendT Thompson
21-JanN SmithB&B SmithM RobertsL MuskM MurrayS Musk
28-JanA Hill A RogersM BondN ThompsonM MurrayN Smith
04-FebS EvertonB L-RogersJ HarperS MuskK TownsendW Brown
11-FebM BondC SmithH ThompsonT ThompsonR TownsendL Musk
18-FebR ColeM GallagherV RogersT LamontR TownsendM Bond
25-FebN SmithA BasharR MatthewsT MatthewsM MurrayT Lamont

Output
DateNameActivity
07-Jan M BondDeacon
28-Jan M BondChurch AM
11-Feb M BondDeacon
18-FebM BondCar Park


<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
[TABLE="width: 636"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Would any one be able to help me out on this as I am stumped on where to start.

Many thanks [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
Hi Iggydarsa, wow this is amazing, does the task perfectly.

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

:)
 
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top