Combining data from 2 sheets that are tracking data over time

Angie Little

I have 2 separate sheets that are tracking different aspects of testing for a group of serial numbers over time. In one sheet (VT6 HTA Life Testing), we are taking measurements twice a day. In the other sheet (Cumulative Cycles By Date), we are tracking number of cycles each day and then have created a cumulative cycle column. For this group of serial numbers, I need to graph RCL measurements vs. number of cycles as the number of cycles increase. In other words, I would like number of cycles on the x-axis, then HTA**RCL measurements on the y-axis for each serial number. I have created this row report but this doesn't seem to be what I need. Any suggestions?

Joseph Aloysias - vSaaS Global

@Angie Little Could you please try this formula,
=INDEX({Cumulative Cycles}, MATCH(
MAX(COLLECT({Cycle Date}, {Serial}, [Serial Number]@row,{Cycle Date}, <= [RCL Date]@row)),
{Cycle Date}, 0
))

Please update the column references and names to match those in your existing sheet.

Joseph Aloysias - vSaaS Global

@Angie Little If the Primary column contains unique values, you can use INDEX-MATCH or COLLECT formulas to retrieve the RCL measurements and number of cycles into separate columns, which can then be used to create a graph.

Let me know if this helps!

Angie Little

@Joseph Aloysias - vSaaS Global I tried Index-Match. I think the issue is that my dates don't entirely line up. In other words, we took RCL readings almost every day but cycle readings less periodically. The result seems to be that my data is reporting separately (both RCL readings & cycles are on the x axis). I've not used a COLLECT formula. I will investigate that but any additional suggestions would be appreciated.

Joseph Aloysias - vSaaS Global

@Angie Little Could you please try this formula,
=INDEX({Cumulative Cycles}, MATCH(
MAX(COLLECT({Cycle Date}, {Serial}, [Serial Number]@row,{Cycle Date}, <= [RCL Date]@row)),
{Cycle Date}, 0
))

Please update the column references and names to match those in your existing sheet.