I have one column (A) with dates and I want to create a column with unique month-year.
I could use the UNIQUE function but that requires creating an extra column (B) with:
= DATE(YEAR(A1),MONTH(A1))
and the final column = UNIQUE(B1:B)
The question is, can I do it without creating the extra column?
As of 2023, both GSheet and Excel have both the UNIQUE function and a BYROW function which is better defined and easier to understand than ARRAYFORMULA. Therefore I'm proposing a new solution that works in both products:
=unique(byrow(A:A; lambda(d; date(year(d); month(d); 1))))
Explanation: the BYROW() function loops over all the dates in the A column, and using the LAMBDA() it constructs a column of dates which replaces, for each date, the day of the month with 1. Then the UNIQUE() function takes only the unique dates.
-- The answer below still works in GSheet, but is a little outdated and does not work in Excel --
Wrap the date expression in an ARRAYFORMULA:
=UNIQUE(ARRAYFORMULA(DATE(YEAR(A1:A10), MONTH(A1:A10), 1))
There is no UNIQUE() function in Excel, it exists only in Google Sheets. You can simulate it using an INDEX/MATCH/COUNTIF scheme of formulas, but it's certainly not as simple as the solution above.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With