compare data later in same table

[Amourous12]

Hi,

I need to read data later in a file and write it to a new column, is there a way to check data more than 1 line away using multi-row formula as I have used this to get the next/previous (previous year) line and see if it is different but it wouldn't be able to check further than the previous year, I would like to check if this has changed at all throughout the list as it can change multiple times. E.g. business column changed 3-4 times over 20 years

ID	Manager	Year	Business
1	tester	2020	tesco
1	tester	2019	tesco
1	tester	2018	tesco
1	tester	2017	lidl
1	tester	2016	lidl
1	tester	2015	asda
1	tester	2014	asda
1	tester	2013	asda
1	tester	2012	homebrand

Where I am checking previous business using multi row formula

ID	Manager	Year	Business	Previous Business
1	tester	2020	tesco	tesco
1	tester	2019	tesco	tesco
1	tester	2018	tesco	lidl
1	tester	2017	lidl	lidl
1	tester	2016	lidl	asda
1	tester	2015	asda	asda
1	tester	2014	asda	asda
1	tester	2013	asda	homebrand
1	tester	2012	homebrand	homebrand

Expected:

ID	Manager	Year	Business	Previous Business
1	tester	2020	tesco	lidl
1	tester	2019	tesco	lidl
1	tester	2018	tesco	lidl
1	tester	2017	lidl	asda
1	tester	2016	lidl	asda
1	tester	2015	asda	homebrand
1	tester	2014	asda	homebrand
1	tester	2013	asda	homebrand
1	tester	2012	homebrand	homebrand

[FinnCharlton]

Hi @Amourous12 , you can achieve this with some summarising and then re-generating the lost rows:

Hope this helps!

[Amourous12]

Hi @FinnCharlton, thanks this has worked, however I have thought of another example as this would more than likely come up in the future for similar tasks, what if the initial table had other columns which had different information in them that are not linked to the date or the Business column but are also required. Would this be possible using your method and joining your new table onto the existing table or no?

E.g

ID	Manager	Year	Colour	reason	Business
1	tester	2020	red	N/A	tesco
1	tester	2019	red	most sales	tesco
1	tester	2018	red	most sales	tesco
1	tester	2017	red	lack of sales	lidl
1	tester	2016	blue	lack of sales	lidl
1	tester	2015	blue	No reason	asda
1	tester	2014	red	increase sales	asda
1	tester	2013	orange	increase sales	asda
1	tester	2012	orange	lack of sales	homebrand

Also what if the years are missing a year, e.g 2014 next line 2012?

[FinnCharlton · Accepted solution]

@Amourous12 , yep you can join it back onto the original table instead:

[Amourous12]

Hi @FinnCharlton, thanks this has worked and sorry for the clarifying questions but what if the value appears multiple times in the table. for example if the business column had tesco again after lidl and another company after the second tesco, this would only show asda after tesco instead of lidl in the first set of rows

ID	Manager	Year	Business
1	tester	2020	tesco
1	tester	2019	tesco
1	tester	2018	tesco
1	tester	2017	lidl
1	tester	2016	lidl
1	tester	2015	tesco
1	tester	2014	tesco
1	tester	2013	tesco
1	tester	2012	tesco
1	tester	2011	tesco
1	tester	2010	asda

[FinnCharlton · Accepted solution]

@Amourous12 fair point, we can fix this with a multi-row formula to introduce an ID to each different instance of a business: