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 IDManagerYearBusiness1tester2020tesco1tester2019tesco1tester2018tesco1tester2017lidl1tester2016lidl1tester2015asda1tester2014asda1tester2013asda1tester2012homebrand Where I am checking previous business using multi row formula IDManagerYearBusinessPrevious Business1tester2020tescotesco1tester2019tescotesco1tester2018tescolidl1tester2017lidllidl1tester2016lidlasda1tester2015asdaasda1tester2014asdaasda1tester2013asdahomebrand1tester2012homebrandhomebrand Expected: IDManagerYearBusinessPrevious Business1tester2020tescolidl1tester2019tescolidl1tester2018tescolidl1tester2017lidlasda1tester2016lidlasda1tester2015asdahomebrand1tester2014asdahomebrand1tester2013asdahomebrand1tester2012homebrandhomebrand

compare data later in same table

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

Workflow

Input

Output

Accepted answers

FinnCharlton

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

Compare Data Later in Table.yxmd

FinnCharlton

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

Compare Data Later in Table.yxmd

All comments

FinnCharlton

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

Hope this helps!

Compare Data Later in Table.yxmd

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

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

Compare Data Later in Table.yxmd

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

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

Compare Data Later in Table.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703