Search code examples
if-statementgoogle-sheetspivot-tablegoogle-sheets-formulaarray-formulas

Format Google Sheet to show only go first instance of each value in column

Can Google sheets format a column using QUERY where it only shows the first instance of each unique value in a column.

For example, I have a column of dates ordered descending. I'd like to have a column for year, where at the beginning of each year in the date column, the year is shown on the year column, but just for the first record. Like a pivot table, but no expanding and collapsing

I can get a year column to show the year on each row, but not just the first (max) date row for each year

Solution

  • try:

    =ARRAYFORMULA(IF((B2:B<>"")*(COUNTIFS(YEAR(B2:B), 
 YEAR(B2:B), ROW(B2:B), "<="&ROW(B2:B))=1), YEAR(B2:B), ))

    0