• 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 a list of unique years from list of dates, without converting to text

Anon9149

New Member
Hi all,

With respect to the attached.

I'm trying to create a mini-model for currency exchange rates. The data is stored in our financial reporting software and is multi-currency and multi-period. Users of this information typically download from the system each time they need a specific rate on a specific date, hence a lot of time being lost to data extraction. The model I'm putting together aims to manipulate all historic data so that the user can quickly find the rate they want simply by making a couple of selections from the DV lists (yellow cells, worksheet 'Table).

As the data spans at least 3 years, I don't want to simply have one DV list containing all the days in these three years, as the user would have to sift through +1000 options to find their date. Hence, I have one DV list for the year selection, and another, dependent DV list that will show only the dates in the selected year.

The problem is this:
- The dates from the system are presented in a non-conventional format in (see worksheet 'System', column C)
- I have to reformat those dates to convert to a standard format (see column H)
- Once converted, those dates still don't appear to function like a normal date, so another conversion is needed (see column I)
- I then extract a list of unique years that appear in column I (see column Q) using the formula =UNIQUE(TEXT(SORT($J$3#,,-1),"yyyy")). Note that I am using the text function here because I don't know how else to achieve the same result. FYI this is the data source for the first DV list (see cell C11, worksheet 'Table')
- Navigating back to worksheet 'System' in column R, I attempt to extract a list of dates in column I, where the year matches the selection made in cell C11 of the 'Table' worksheet using the following formula =FILTER($J$3#,YEAR($J$3#)=Table!$C$11)

I suspect the root of the problem is that the data in column Q is formatted as text. I suspect this because if you replace the element of the above formula that I have presented in bold text, with the value 2022, then column R does then generates a list of all the dates in the year 2022.

As the root of the problem seems to be that I am converting to text in order to extract a unique list of years, I wonder if anyone can help find a better solution that doesn't generate errors down the road i.e. in column R

Thanks in advance,

A

PS I've had to drastically reduce the data set by hundreds of thousands of lines as it was too lare to upload. May or may not be relevant to know that.
 

Attachments

  • FEX.xlsx
    905.4 KB · Views: 6
Thank you Bosco. This works perfectly, but I can't work out why. Would you mind briefly explaining it to me?
 
One way, in the first link. Green cells in row 2 of System sheet are changed as are all the cells in the data body part of the table on the Table sheet. Hope it gives you ideas.

In the second link below, a Power Query/Pivot table possibility.
I've added a pivot table to the right of your existing results table so that you can compare them.
For the pivot table, you only need the table in columns A:E of the System sheet, nothing else.
On the Table sheet, you only need the pivot and its slicers, nothing else. No formulae.
The slicers will hide options as you select options in other slicers, so to see all the possibilities clear the slicers first by clicking on the clear filter buttons:

82485

As slicers allow you to choose more than a single date, when you select say a whole month, the results will show an average of that month.
 
Last edited:
Thanks p45cal!

I'll admit that I've only used slicers infrequently, and only recently in Power BI. In fact, I did try to add slicers in this exercise but it quickly became apparent that having data in a table was a prerequisite. When I converted the arrays to tables, the formulas that reference dynamic arrays (i.e. those ones suffixed with '#') turned into #SPILL! errors, so I quickly abandoned this, as this was ultimately intended to be a quick tool that supported larger modelling exercises.

I'm very intrigued to learn that my solution doesn't require any formula however, so I will be looking at this in detail tomorrow as it sounds like it could streamline the my approach to similar, future exercises.

Thank you also for investing the time that you evidently have done - it's is very much appreciated and it's always great to see a new and improved method of working.
 
Thank you Bosco. This works perfectly, but I can't work out why. Would you mind briefly explaining it to me?

=FILTER($J$3#,YEAR($J$3#)=0+Table!$C$11)

=YEAR($J$3#) return a list of year in NUMBER e.g. ........2022,2022

But

=Table!$C$11 return a TEXT e.g. "2022"

So,

Add a 0, enforce TEXT convert to NUMBER, the formula become>>

=0+Table!$C$11 return a NUMBER e.g. 2022

Then

YEAR($J$3#)=0+Table!$C$11 can match. :)

Regards
 
The attached is a formula-based approach that returns the latest FEX rate for the specified date (sometimes that is more than a year before the requested date). It could also be done using XMATCH but, there too searching for the matching date or the largest smaller date would make sense.
 

Attachments

  • FEX.xlsx
    732.5 KB · Views: 2
Back
Top