topic Re: How can I use a column formula to return the value of a specific row in another column? in Discussions

How can I use a column formula to return the value of a specific row in another column?

user8421 — Wed, 04 Dec 2019 22:56:26 GMT

Here is an example data table with the 4th column "Desired Formula Column" showing the outcome I am trying to get.

Group	Sample Number	Data Value	Desired Formula Column
A	1	35	35
A	2	2	35
A	3	1	35
B	1	22	22
B	2	4	22
B	3	2	22
C	1	40	40
C	2	2	40
C	3	3	40

I am looking for a column formula that will enter the data value for sample 1 for each group.

Any help appreciated!

Re: How can I use a column formula to return the value of a specific row in another column?

Thierry_S — Thu, 05 Dec 2019 14:05:14 GMT

Hi,

Here is a simple formula that I use all the time:

If( Row() == 1,
	:Data Value,
	If( Lag( :Group, 1 ) != :Group,
		:Data Value,
		Lag( :Desired Formula Column, 1 )
	)
)

Re: How can I use a column formula to return the value of a specific row in another column?

ron_horne — Wed, 04 Dec 2019 23:26:59 GMT

Hi @user8421

this is a fun question try adding a column to the table with this formula.

If( :Sample Number == 1,
	:Data Value,
	Lag( :Column 5, 1 )
)

this is not robust but if you do not change row order you should be fine. otherwise just delete the formula once you have the numbers where you want them.