Choose Language Hide Translation Bar
Highlighted
user8421
Level I

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

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

 

GroupSample NumberData ValueDesired Formula Column
A13535
A2235
A3135
B12222
B2422
B3222
C14040
C2240
C3340

 

 

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

 

Any help appreciated!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Thierry_S
Level VI

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

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 )
	)
)
Thierry R. Sornasse

View solution in original post

2 REPLIES 2
Highlighted
Thierry_S
Level VI

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

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 )
	)
)
Thierry R. Sornasse

View solution in original post

Highlighted
ron_horne
Super User

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

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.

123.png

Article Labels

    There are no labels assigned to this post.