How do I align user's role based on a point in time occurrence?

Matt Foss

I manage a vendor relationship that includes tracking the usage of their services on a monthly basis. The vendor services are available to multiple internal groups and part of the reporting is tracking the percentage of total services that each group utilized in a given month. On occasion, a user may transfer to a new group (and this may occur multiple times over the course of a year).

I'm trying to develop a method that will:

1. Recognize when a user transfer occurs to a new group after a certain date.
2. Reflect the user's new group for any usage after the transfer date.
3. Continue to identify the prior group for any occurrence before that date.

Each user is identified by a unique ID and when a new month is pasted in, the raw data sheet references another sheet ("name sheet") that stores the user's full name and group they are aligned to. While I can typically use index/match to bring in the details, the challenge is when the user changes roles. I'd like to avoid having to "paste as values" for the prior role and use the index/match after a certain date (there are 50+ unique users and handing off this process to others would get too complex).

Any ideas? Please see a simple screenshot below of how the raw data sheet looks. The column formulas in "User Name" and "User Group" reference the name sheet using index/match. For this example, are there any suggestions on how I can use formulas in the raw data and name sheet to recognize that on 08/17/21 user mfoss transferred to the "Sales" group. Reminder that I would still need the dates prior to 08/17/21 to reflect the old role of "Back Office."

Note: I've considered having other columns on the background sheet to indicate a date change and tie it into existing formulas, but am having trouble coming up with a reliable method that at MOST just requires an administrator to input the date of a change and new group and the formula does the rest.

Thanks in advance! Happy to answer additional questions as this is a little bit on the complex side.

Matt Foss

Thanks so much for the recommendation Genevieve! To answer your question, there are about 20 different groups.

I ultimately took the process another direction and found a workable solution with formulas!

1. As a starting point, on the "name sheet" I first needed to add the to/from dates for the group of each user. Group changes do occur throughout the year, but typically only impact about 8 or so users.
2. Workflow wise, when a user is identified as changing groups, you simply update an end date for the initial group (by default the end date always uses today's date). You then start a new column with a start date for new group and the end date auto populates with today's date.
3. From here, there's a separate column for the "new group" and there's also a mirrored column system if there's a 3rd group change (usually doesn't happen).

The raw data always contains the date a service was pulled. I also mirrored the start/end date and additional group columns that pull in based on an index match from the "name sheet" into the "raw data sheet." After mirroring those, I use an IF(AND statement in conjunction with index match that checks the date the service was pulled against the date ranges indicated (the user ID is the anchor data point with the date of service pull driving the IF logic). See the screenshot below. Ultimately, I think this ended up being a great workable solution!

Best regards,

Matt

Genevieve P.

Hi @Matt Foss

How many potential Groups are there?

Formulas are unable to check cell history so they'll always pull the most recent group. However I'm thinking that we may be able to use the new Change Cell Value workflow to input a current value as text without syncing it to the other sheet moving forward.

For example, you could trigger the workflow based on the Date of Service Pull so that in the evening of that day it inputs the same value that the formula pulled through for that date into a new column. You could also set it up to trigger as soon when a new row is added.

You'll see that for each Group that the formula pulls through, I have a separate Condition block and Action to identify the value I want in this new column.

In my test sheet, the column for the workflow is called "New Column - Group":

Then as the month goes on and people change, the formula will update with the new group and the workflow will add in a new text value to this column, but the old values will stay.

In this example, I changed over to "Best Group":

Notice that my column formula in "User Group" now shows the current group present in the source sheet for all rows, but the helper column displays the old value that the workflow input. Because of this, I would recommend hiding the User Group column so it's not confusing on the sheet (it can still be referenced in the workflow if it's hidden).

Let me know if you think this will work for you!

Cheers,

Genevieve

Matt Foss

Thanks so much for the recommendation Genevieve! To answer your question, there are about 20 different groups.

I ultimately took the process another direction and found a workable solution with formulas!

1. As a starting point, on the "name sheet" I first needed to add the to/from dates for the group of each user. Group changes do occur throughout the year, but typically only impact about 8 or so users.
2. Workflow wise, when a user is identified as changing groups, you simply update an end date for the initial group (by default the end date always uses today's date). You then start a new column with a start date for new group and the end date auto populates with today's date.
3. From here, there's a separate column for the "new group" and there's also a mirrored column system if there's a 3rd group change (usually doesn't happen).

The raw data always contains the date a service was pulled. I also mirrored the start/end date and additional group columns that pull in based on an index match from the "name sheet" into the "raw data sheet." After mirroring those, I use an IF(AND statement in conjunction with index match that checks the date the service was pulled against the date ranges indicated (the user ID is the anchor data point with the date of service pull driving the IF logic). See the screenshot below. Ultimately, I think this ended up being a great workable solution!

Best regards,

Matt

Genevieve P.

Hi @Matt Foss

I'm so glad you found a workable solution! Thanks for posting your answer and process, this is very helpful.

Cheers,

Genevieve