Formula to pull data from two lines into one line (same sheet)

alexis.ray89371

Hi,

I wanted to see if it is possible to have a formula that uses data from two different rows within the same sheet based on certain criteria? Any help would be much appreciated. Thank you!

I tried to explain it via the visualization below.

Paul Newcome

Try something like this:

=IFERROR(INDEX(COLLECT([Lab Temp Column]:[Lab Temp Column], Type:Type, @cell = "LAB", Date:Date, @cell = Date@row, [LAB Number]:[LAB Number], @cell = [LAB Number]@row), 1), 0) - IFERROR(INDEX(COLLECT([Desk Temp Column]:[Desk Temp Column], Type:Type, @cell = "Desktop", Date:Date, @cell = Date@row, [LAB Number]:[LAB Number], @cell = [LAB Number]@row), 1), 0)

Paul Newcome

You missed the Type:Type range in each of the COLLECT functions. That is where we specify to pull the "LAB" for the first and the "Desktop" for the second.

Paul Newcome

Try something like this:

=IFERROR(INDEX(COLLECT([Lab Temp Column]:[Lab Temp Column], Type:Type, @cell = "LAB", Date:Date, @cell = Date@row, [LAB Number]:[LAB Number], @cell = [LAB Number]@row), 1), 0) - IFERROR(INDEX(COLLECT([Desk Temp Column]:[Desk Temp Column], Type:Type, @cell = "Desktop", Date:Date, @cell = Date@row, [LAB Number]:[LAB Number], @cell = [LAB Number]@row), 1), 0)

alexis.ray89371

thank you for responding although I don't think it quite gets me there. i used the below formula:

=IFERROR(INDEX(COLLECT([Temperature Taken in Laboratory by Thermostat]:[Temperature Taken in Laboratory by Thermostat], Date:Date, @cell = Date@row, [LAB Number]:[LAB Number], @cell = [LAB Number]@row), 1), 0) - IFERROR(INDEX(COLLECT([Temperature taken from Metasys Desktop]:[Temperature taken from Metasys Desktop], Date:Date, @cell = Date@row, [LAB Number]:[LAB Number], @cell = [LAB Number]@row), 1), 0)

In the example below the variance should equal 4

Paul Newcome

You missed the Type:Type range in each of the COLLECT functions. That is where we specify to pull the "LAB" for the first and the "Desktop" for the second.

alexis.ray89371

you are absolutely right! thank you. appreciate your help. 😁

Paul Newcome

Happy to help. 👍️