←Back to Beyond Market Intelligence
•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Converting a table into a rota/grid

Our take

Transforming a staff activity table into a more readable grid can streamline your workflow and enhance clarity. By transposing the data, you can create a layout that organizes activities by date and time, making it easier to identify overlaps and manage schedules. While nested IF statements and functions like XLOOKUP may seem like viable solutions, there are more elegant methods available. Let's explore innovative approaches that simplify this process, ensuring your staff’s activities are easily accessible and efficiently displayed.

I have a table that I am using to track staff activity.

Each row has 1 staff member and a date, so each staff member has 365 rows each for the year. There's then multiple columns determining the different types of activity they can be doing (there's then conditional formatting to check if these clash or to inform staff which should be cancelled when they do overlap etc.)

Name Date Activity AM Activity PM Special Activity AM Special Activity PM
John Smith 11/3/26 W OFF C
John Smith 12/3/26 W W
Sarah Jones 11/3/26 AL AL
Sarah Jones 12/3/26 W C

Is there a way to transpose or translate this into a more readable grid?

11/3/26 AM 11/3/26 PM 12/3/26 AM 12/3/26 PM
John Smith C OFF W W
Sarah Jones AL AL W C

I imagine with enough nested if statements and an xlookup or something for the date and names, I could do it... something like this, where Special Activity goes first, but if it is blank, then whatever is in Activity, and so on through the different activity columns?

Does anyone have any ideas for a more elegant solution?

submitted by /u/Chewitt321
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Tagged with

#rows.com