Extracting Data from a schedule to make multiple schedules

G

gazz57

New Member
Joined
Nov 24, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
HI

I have a an excel spreadsheet with Locations along row one and dates running down column A row 2 to 32; 1st, 2nd, etc....

This setup is to generate a schedule for people to be in a particular location on a particular day in a month. This is completed manually every month. Often I might duplicate someone having them in 2 locations on the one day therefore nobody turns up at one of those locations which is one issue. The big issue i have is then i need to make an individual schedule for every person in the schedule i.e produce another work sheet to show dates in column A and the location that person is in beside the date. Again I do this manually.

I've been trying to figure out a better way to do this, possibly using Macros to automate the generation of individual rotas for each person etc.. but have been unsuccessful. Im not sure Excel can do what i'd like it to do. Maybe Access would be better?

Would anyone have any suggestions on how to generate individualised rotas based on the overall worksheet? See a sample worksheet below:

JunLocation 1Location 2Location 3Location 4Location 5Location 6Location 7Location 8Location 9Location 10
Sun 1strotaColin
Mon 2ndElinorDanielSiobhanConorMeganJohn McMaireadYvonneMichaelCath/Heather
Tues 3rdElinorAdrianKellyConorMeganJohn mCKeithYvonneDanielCath
Wed 4thMichaelColinKellyConorMeganJohn Mc/OdhranKeithAdrianDanielCath
Thur 5thElinorColmKellyConorMegan/MartySiobhanKeithYvonneDanielHeather/Lynsey
Fri 6thElinor/Michael/JoyceDanielKellyConorMeganAdrianJohn McYvonneMichaelCath
Sat 7thMichaelColinKellyAdrianSiobhanMaireadKeithYvonneJohn SJoyce
Sun 8thJoyce rotaGary rota
Mon 9thMichael/Joyce/SineadColinKellyConorLynseyMairead CJohn McYvonneDanielCath
Tues 10thMichael/Elinor/OdhranDanielKellyConorMegan/SiobhanJulieKeithYvonne/AdrianJohn SCath/Eimear*
Wed 11thMichael/ElinorDanielKelly/AdrianConorMegan/SiobhanJulieKeithMaireadStephenCath/Gary
Thur 12thElinor/JoyceMartyKellySiobhanMeganJulie/OdhranKeithAdrianStephenColin
Fri 13thMichael/Elinor/JoyceDanielKellyConorMeganJulieKeithAdrianStephenCath/Heather*
Sat 14thElinorDanielAdrianConorLynseyJulieKeithYvonneStephenCath
Sun 15thDaniel
Mon 16thMichael/EimearDanielSiobhanConorLynseyJulieMaireadYvonneStephenCath
Tues 17thMichael/Elinor/EimearColmKelly/SineadConor/DarranMeganJulieKeithYvonneStephenCath/Heather
Wed 18thMichael/Elinor/JoyceDaniel/ColmKelly/ConorDarranMegan/SiobhanJulie/MaireadKeithYvonneStephenCath/Heather
Thur 19thOdhran/JoyceDaniel/ColmKelly/ConorDarranMegan/MartyJulie/Mairead CKeithYvonneStephenCara/Lynsey
Fri 20thMichael/Sinead/ColinDanielKellyDarranMegan/ConorJulieKeithYvonneStephenCath/Lynsey
Sat 21stMichaelColinKellySiobhanMeganMaireadKeithAdrianJohn SJoyce
Sun 22ndrotaGeorge
Mon 23rdMichael/Eimear/OdhranDanielKellyJoyceLynsey/SiobhanMaireadKeithYvonne/AdrianStephenCath/Heather*
Tues 24thMichael/Joyce/OdhranColmKelly/ColinDarranConorJulieKeithYvonne/AdrianJohn sCath
Wed 25thMichael/OdhranDaniel/ColmKelly/sIOBHANDarranConorJulie/Mairead*KeithAdrianStephenCath/
Thur 26th/JoyceDaniel/ColmKellyDarranConor/SiobhanJulieOdhranYvonne/AdrianStephenCara
Fri 27thMichael/Eimear/DanielKellyDarranConorJulieKeithYvonneStephenCath/Heather
Sat 28thDanielJohn/AdrianConorLynseyJulieKeithYvonneStephenCath
Sun 29thEdel B
Mon 30thMichael/EimearDanielKellySiobhanMeganJulieMaireadYvonneStephenCath/Heather
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Sometimes, the best solution is to use a different tool.
I think AirTable would be a perfect use case for this type of project.
You could get the results you're looking for much quicker and it would be much easier to manage down the road.

Check it out.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Update Variable Data from Multiple Worksheets in a Master Sheet
2
Replies
11
Views
738
mumps
mumps
S
  • Question Question
How do I make a line graph from this data?
Replies
3
Views
436
awoohaw
awoohaw
A
  • Solved
Using VBA to loop through multiple Word Templates and fill them in with Excel Data
2
Replies
14
Views
1K
John_w
J
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
294
Jagadeshrao
J
M
  • Question Question
Creating a calendar by matching names and dates from multiple sheets
Replies
6
Views
2K
mjreezy
M

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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