Search code examples
google-sheetsgoogle-query-language

Get unique values in a range where each value depends on the other cell

Here is an example table columns A,B,C where A - post time, B - name of a team, D - chosen date

10.08.2014 10:21:22, Name1, 25Mar
10.08.2014 10:21:23, Name1, 25Mar
10.08.2014 10:21:24, Name2, 28Mar
10.08.2014 10:21:25, Name2, 28Mar
10.08.2014 10:21:26, Name1, 28Mar

I want to get from the data above the following:

10.08.2014 10:21:22, Name1, 25Mar
10.08.2014 10:21:24, Name2, 28Mar
10.08.2014 10:21:26, Name1, 28Mar

Thus I want to select uique records by 2 columns: name(B) and date(C), however from the non-unique values based on B and C column, choose the one with the earliest time (column A).

How I can achive that in google spreadshits?

Solution

  • Assuming your data starts in row 2, try:

    =ArrayFormula(vlookup(unique(B2:B&C2:C), {B2:B&C2:C,A2:C}, {2,3,4}, 0))

    and see if that works. Note: depending on your locale the formula may need to be changed to:

    =ArrayFormula(vlookup(unique(B2:B&C2:C); {B2:B&C2:C\A2:C}; {2\3\4}; 0))

    and.. in case the data in col A is not sorted already...

    =ArrayFormula(vlookup(unique(B2:B&C2:C), sort({B2:B&C2:C,A2:C},2,1), {2,3,4}, 0))