Organizing Data Help Requested

[the914man]

Hi there - I'm looking for help trying to solve this problem below.

I have about 6 billing codes. Each month, there is a status update about the progress of the billing code and when the project is going to be sold/kicked off-etc.

For example, for billing code 1000 the first check-in was in Jan '21.

On Jan '21, the project state was "Not Started" and the work was expected to start 1/3/2021 and finish 5/6/2022. 

Subsequently, there was a check-in for Feb 21. On Feb '21, the project state was "In Progress" and was expected to start 4/1/2021 and finish 2/15/2022.

I want to create a workflow that goes through all of the billing codes and tells me the expected start and finish dates for the first time the status is updated to "In Progress." For context, there are about 40 different check-in dates but the image shows 3 as an example.

If someone could provide commentary on how you would set it up that would be very helpful!

[Qiu · Accepted solution]

@the914man 
The Transpose tool will be good for the large number of columns.

Then we can use the Tile tool to group by the Date under each Billing Code.

[the914man]

Awesome - Will this logic hold even if a row of data doesn't have an "In Progress"

Also, what is the logic if I want the last of a particular state. For example, last "Not Started" 

Appreciate how detailed you were. Really appreciate it.

[Qiu]

@the914man 
Since the Filter tool filters out any row that is NOT "In Progress" so the row does not have an "In Progress" will not be included in the output.

If you want llast "Not Started" , you can change the value in the filter tool "Not Started" and in the Sample tool, choose Last row of data.