List unique years from a set of dates in google sheets
I am currently achieving the process of extracting unique years from a range of dates with the use of helper columns shown here:
Is it possible to achieve the same using just a single formula?
Here are the three formulas I am currently using:
S column: =UNIQUE('Earnings Input'!A2:A)
T column: =YEAR(S1)
U column: =UNIQUE(T1:T)
Appreciate any help I can get!
Solution
As there is no link to your spreadsheet, this is untested, but you can try this:
=ArrayFormula(SORT(UNIQUE(YEAR(FILTER('Earnings Input'!A2:A,ISNUMBER('Earnings Input'!A2:A))))))
FILTER( ,ISNUMBER( )) filters in only values that could reasonably be a date.
YEAR gets the years for those entries.
UNIQUE reduces to the unique listing
SORT assures the results will be in ascending order.
- Google Sheets - returning Nth result with multiple matching criteria
- IF Status Formula Google Sheet Approval
- Google apps script: autoResizeColumn works incorrectly not works as expected
- How to find common words in two columns in Google Sheets
- Unable to open Google xlsx spreadsheet / Also Google Drive permission Blocked
- Calculate difference of values in specific range of time in Excel
- Select from series of columns without blanks and then where Google Sheets query
- Allow user with edit privileges to run scripts on a spreadsheet owned by me
- Delete last row with value then find
- How can I save the URL(s), after making a copy (event) of a Google Sheets spreadsheet?
- How to make previous dates data from one tab appear in another tab
- Is it possible to generate a summary table of another table?
- Replace form response email address with name from master list
- How do I show data from a spreadsheet? it shows "undefined"
- Extract multiple sparsely packed responses to yes/no identifiers while preserving row information
- getLastRow() in Apps Script returns error: "This operation is not allowed on cells in typed columns."
- I can't convert 46809654.36 into a date and time value in Google Sheets
- Google Spreadsheet Last X Rows
- Select rows that contain a specific dropdown value
- How to negate 'isblank' function
- Is it possible get Image from Google Sheets to Google Docs Template when creating
- Byrow formula with reference to last row and last column
- Dynamically update variable in Spreadsheet popup UI element from google Apps Script
- Save timestamp time in a cell and then the timestamp edit time in another cell
- find a column where specific rows contain specific values
- Inserting images into a Sheet as BlobSource using Google Apps Script
- Slack to Google Sheets integration "dispatch failed" error from slash command
- How to make an auto-timestamped row in Google Sheets
- Get the list of all spreadsheets associated with Google account using Sheets API v4
- Conditional criteria in a query formula