Pulling data based on most current entry and multiple criteria with other entries on same day?

CaptainPlanet

I am trying to extract data from a sheet and summarize the most current data point based on:

1) Matching a reference cell (Sample Location).

2) Ignoring blank entries since some rows may have data in some columns but not others.

3) Accounting for different reference cells that have data entered on the same date.

The below link is pretty close to the solution I need, however, the issue arises when I have multiple entries on the same day. I need to be able to pull only the data for that day specific to the reference cell, but the formula is pulling the first entry if there are multiple on the same day, with the refernce cell criteria being met.

 Return the lowest cell that isn't blank based on MAX date. — Smartsheet Community

=INDEX({1,2,3-TCP Column Second Sheet}, MATCH(MAX(COLLECT({Sample Date Column Second Sheet}, {1,2,3-TCP Column Second Sheet}, @cell <> "", {Sample Location Column Second Sheet}, $Sample Location Column Firtst Sheet)), {Sample Date Column Second Sheet}, 0))

 

The first screen shot is the summary sheet where data needs to be pulled into. The sampling location column is the reference cell I want to utilize to pull data from the second screenshot. For example, I want to collect the most recent data entry (excluding blank entries) for the 1,2,3-TCP column in the second screenshot for AD Well 2 and have this data point populated in the first screenshot into the AD Well 2 row in the 1,2,3-TCP column. The issue with using the above formula is that it matches all the criteria, but since the sample date is the same for AD Well 1 and AD Well 2, it pulls the first data entry it comes across for AD Well 1 (value of 1) instead of AD Well 2 (value of 3)

First Sheet

 Second Sheet

 

L_123

this tends to work better with system columns. Have you tried using a created or an autonumber, then using that as your reference? That way you can use max/min to reference the earliest or latest submissions much more easily.

CaptainPlanet

Than you, L_123. I ended up adding a helper column and used the below formula and got it to work.

INDEX(COLLECT({Sheet - Data Input - Water Quality Range 5}, {Sheet - Data Input - Water Quality Range 1}, [Sample Date - 1,2,3-TCP]@row, {Sheet - Data Input - Water Quality Range 2}, [Sample Location (Drop Down List)]@row), 1)