Extract unique records with date criteria: between 2 dates

[Phlebas]

Hello forum,

I have been try to extract unique values of account numbers from a large list of varied data based on two dates that will vary depending on the dates on another sheet. I have started by simply trying to extract the account numbers above the first date criteria but have run into a problem I cannot seem to get around, or understand why. The formula I have used is

=IFERROR((INDEX('[Costing Report (FY 2018-2019).xlsx]Year To Date'!$A$2:$A$29,MATCH(0,IF(($B$3>='[Costing Report (FY 2018-2019).xlsx]Year To Date'!$D$2:$D$29),COUNTIF($B$17:B17,'[Costing Report (FY 2018-2019).xlsx]Year To Date'!$A$2:$A$29),""),0))),"")

The list starts with dates from 02 July 2018 and the start criteria date in cell B3 is 01 July 2018. This though returns a blank cell. If I change the start criteria date to 02 July 2018 though is returns what I would expect.

Sorry I haven't attached the file but I don't seem to have permission to do so and being new here am not sure how to get this permission.

Any help to resolve this, and extend the formula so I can calculate between two dates wold be much appreciated.

Thanks
Gareth

 

[Fazza]

hello, Gareth

sounds like a good situation for a parameterised query. just like this one [URL]http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/[/url]
uses no formulas & no codes. works well with huge datasets

to get a list of unique account numbers be sure to include the DISTINCT keyword

SELECT DISTINCT account_numbers
FROM YourData
WHERE YourDateField Between [Enter a start date] And [Enter an end date]

 

[Phlebas]

Hi Fazza,

Thanks for the reply. I'll be honest I have never heard of Parameterised Queries before but I'll have a look into it and see if it meets my needs. For my spreadsheet getting the account number is only the start of further calculations from other sheets as well.

Thanks

Gareth

 

[Fazza]

could be a steep learning curve, Gareth

pivot tables are good for working with data. I'm assuming you have data set up like database tables. such as headers in row 1 & then data under that in a contiguous block

if so, a really simple approach is to add another column to the data table. header "ShowIt". Populate this new field with a formula that returns TRUE for dates you want in the report & FALSE for others. Then make this a page field of the pivot table, filtered to TRUE.

this may not suit your situation. likely there are very many ways to do what you want and without knowing lots of details it is difficult to advise. all the best, Fazza