Hello All,
Thanks in advance for any help you can give me.
I need to extract a unique list of names from two columns of names, but only names that fall within a given date range. I would also like for the extracted list to be sorted alphabetically a-z.
The datasheet is a list of "deals" for various salespeople. Sometimes more than one sales person works on the same deal, thus the reason for two columns of names (Salesperson1 & Salesperson2). Often the Salesperson2 column will be blank. This is a growing list of deals, so ideally the solution would be dynamic.
I've created multiple dynamic named ranges, one each for Salesperson1 & Salesperson2. The current formula I have will extract a unique list of names from both columns, but I have not been able to figure out how to add in the extra criteria for the date range. Here is the array formula I'm using currently:
The above sits in cell B4, and is copied down the column past the likely number of rows needed for the list of names.
I have a drop down list for the start date located at cell J1 and the end date at cell M1 in the same sheet. On the datasheet, column A starting at row 3 contains the dates associated with each deal. I have a dynamic named range for those dates named "DealDates".
I've tried to add in additional criteria in the "Countifs" portions of the formula using something like this:
But it doesn't work, I just end up with blank results:
I'm probably missing something easy, or else I'm going about it all the wrong way.
I could add in helper columns if needed, but would prefer a completely contained formula if possible. The list of deals is added to daily and we plan to keep about 13 months of data on it, so something around 10,000 rows will end up in the data sheet of deals.
Other information:
OS: Win7
I'm using Excel 2016, but I need to accommodate people using Excel 2010 & 2013.
Any help or pointers you can give me would be much appreciated.
Patrick
Thanks in advance for any help you can give me.
I need to extract a unique list of names from two columns of names, but only names that fall within a given date range. I would also like for the extracted list to be sorted alphabetically a-z.
The datasheet is a list of "deals" for various salespeople. Sometimes more than one sales person works on the same deal, thus the reason for two columns of names (Salesperson1 & Salesperson2). Often the Salesperson2 column will be blank. This is a growing list of deals, so ideally the solution would be dynamic.
I've created multiple dynamic named ranges, one each for Salesperson1 & Salesperson2. The current formula I have will extract a unique list of names from both columns, but I have not been able to figure out how to add in the extra criteria for the date range. Here is the array formula I'm using currently:
The above sits in cell B4, and is copied down the column past the likely number of rows needed for the list of names.
I have a drop down list for the start date located at cell J1 and the end date at cell M1 in the same sheet. On the datasheet, column A starting at row 3 contains the dates associated with each deal. I have a dynamic named range for those dates named "DealDates".
I've tried to add in additional criteria in the "Countifs" portions of the formula using something like this:
But it doesn't work, I just end up with blank results:
I'm probably missing something easy, or else I'm going about it all the wrong way.
I could add in helper columns if needed, but would prefer a completely contained formula if possible. The list of deals is added to daily and we plan to keep about 13 months of data on it, so something around 10,000 rows will end up in the data sheet of deals.
Other information:
OS: Win7
I'm using Excel 2016, but I need to accommodate people using Excel 2010 & 2013.
Any help or pointers you can give me would be much appreciated.
Patrick
