• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Unique Year and Month

cyliyu

Member
I have one table column that contains a list of date.
I wanted to export each year and month to another table and ignore duplicated Year/Month.

i.e. If I have 01/02/2024, 02/02/2024 , 15/02/2024 and so on, the result should have only one row that contain year 2024 and month Feb

Please see the sample in the attached file and the expected results.
 

Attachments

  • Sample.xlsx
    12.8 KB · Views: 6

cyliyu

I have one table column that contains a list of date.
Your expected results seems to be more than You've wanted.
I wanted to export each year and month to another table and ignore duplicated Year/Month.
How about this sample?
 

Attachments

  • Sample.xlsx
    16.5 KB · Views: 5
Thanks, vletm.

I apologize for not mentioning that I'm seeking help of a formula rather than a pivot table.

This is because I have numerous columns with other information that users must enter after the Year and Month have been extracted out to another table.
 

cyliyu

Question:
How do You prevent that Year & Month rows will keep same after You've gotten those .. someway?
Could Your expected results has only one column ... eg Feb-2024?

As written in Forum Rules
  • When drafting a question, try and lay out the question in a clear and concise way.
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
 
In the attached, formula in single cell N3:
Code:
=LET(theDates,Table1[Date],myDates,TEXT(UNIQUE(DATE(YEAR(theDates),MONTH(theDates),1)),"yyymmm"),HSTACK(SEQUENCE(ROWS(myDates)),LEFT(myDates,4),RIGHT(myDates,3)))
Also Power Query table at cell J2. If data changes in source table, then right-click the results table and choose Refresh.
 

Attachments

  • Chandoo55350Sample.xlsx
    21.9 KB · Views: 8
Last edited:
In the attached, formula in single cell N3:
Code:
=LET(theDates,Table1[Date],myDates,TEXT(UNIQUE(DATE(YEAR(theDates),MONTH(theDates),1)),"yyymmm"),HSTACK(SEQUENCE(ROWS(myDates)),LEFT(myDates,4),RIGHT(myDates,3)))
Also Power Query table at cell J2. If data changes in source table, then right-click the results table and choose Refresh.
Thanks, p45cal.
 
Hi p456Cal,

How can I modify this formula so that it works in a Excel Table? Otherwise, I'll receive a #SPILL! error.

=IFERROR(TEXT(UNIQUE(FILTER(DATE(YEAR(Table1[Date]), MONTH(Table1[Date]), 1), Table1[Date]<>"")), "yyymmm"), "")
 
Dynamic array formulae which return multiple rows don't work in tables.
Why does your result need to be in a table?
 
Noted with thanks.
I am using Table most of the time.
I will stick to range.

Thanks again for your help.
 
Back
Top