Creating formula to identify change based on record id

[kw]

Hello,

Is there a way to have a column created that identifies when a change occurs in data based on sorted date and another column. The data is grouped by building and sorted on date. I want to count if a change has occurred. For each building I want to calculate the number of color changes based on the sorted date.

Example

Data set

Building	Color	date
23	G	12/16/2015
23	G	1/17/2016
23	R	2/18/2016
23	G	3/1/2016
56	R	3/1/2016
56	G	3/12/2016

required outcome

Building	Color	Date	Change
23	G	12/16/2015	0
23	G	1/17/2016	0
23	R	2/18/2016	1
23	G	3/1/2016	1
56	R	3/12/2016	0
56	G	3/1/2016	1

[RodL · Accepted solution]

You would use the Multi-Row Formula tool and create an expression that compares the color in the current row with the color in the previous row (i.e., Row-1) and group by building.

[kw]

Thank you... After playing around, I found the tool at the same time you responded...

It was in front of me the entire time.

ha ha :-)

[Ganbatte]

Hello,

I am new to the community.

I have the same issue. Can you show me more detail for the solution such as sharing the workflow?

Thank you very much.

[AngelosPachis]

Hi @Ganbatte 

Have a look at the attached workflow, I may not have grouped on building but the solution is the same.

Hope that helps

[Ganbatte]

Thank you @AngelosPachis for your quick turnaround. I appreciate your help.