Dynamic formula to filter a list with duplicates between two dates and also sort the results

I have a table of data where every record has a date, and a few other data columns as well.  What I want to do is have formulas that will find each row in the table that falls between two dates and return the data in those rows sorted ascending by date.  There may be duplicate dates in the table, and if that is the case I would like those duplicate dates to be secondarily sorted ascending by the data in one of the other columns.  I want to do this with formulas only and not macros or any of the manual filter/sort features.

Ex. of what I am looking for:

Original table:

Date            Region            Sales
3/30/2012      West            $41.00
3/31/2012      MidWest            $16.00
4/1/2012            West            $23.00
4/2/2012            MidWest            $43.00
4/3/2012            West            $11.00
4/4/2012            West            $44.00
5/4/2012            MidWest            $65.00
5/5/2012            MidWest            $24.00
5/6/2012            MidWest            $26.00
4/1/2012            MidWest            $43.00

For dates between 4/1/12 & 4/30/12 result should be:

Date            Region            Sales
4/1/2012            MidWest            $43.00
4/1/2012            West            $23.00
4/2/2012            MidWest            $43.00
4/3/2012            West            $11.00
4/4/2012            West            $44.00

Note I would like the 4/1/2012 Midwest record was brought to the top of the table, and then subsorted ascending by region.


By searching the web, I found the following video on youtube that will bring all the records between two dates.  This formula is pretty good because brings all the data individually for duplicate date rows (if two rows have the same dates, it still brings the data from the two separate records for the data in the other columns).  However the problem with this formula is that it brings the data in the order that they appear in the original table (the video appears to bring the records sorted, but it is only because the original data table is already sorted):

Video:
http://www.youtube.com/watch?v=6jcqN3swdW8
Actual spreadsheet for this is here (tab=894an):
http://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT-890-895.xlsx

So from my example it brings:

Date            Region            Sales
4/1/2012            West            $23.00
4/2/2012            MidWest            $43.00
4/3/2012            West            $11.00
4/4/2012            West            $44.00
4/1/2012            MidWest            $43.00

Note the 4/1/2012 Midwest record remains at the bottom of the table.

I also found the following page which shows how to bring the data in a table containing duplicates into a sorted list.  By changing istext() to isnumber() everyewhere it can be sorted by the numbers instead of the text.  I can adapt this formula to bring the data in other columns in the same order which is good.  The problem with this formula is it brings all the data from the first row only for duplicate date rows (if two rows have the same date, it brings the data from the first record for the data in the other columns):
http://www.get-digital-help.com/2009/10/26/extract-and-sort-text-cells-from-a-range-containing-both-numerical-and-text-values/

So from my example it brings:

Date            Region            Sales
4/1/2012            West            $23.00
4/1/2012            West            $23.00
4/2/2012            MidWest            $43.00
4/3/2012            West            $11.00
4/4/2012            West            $44.00

Note the 4/1/2012 Midwest record is replaced by a duplicate of the 4/1/2012 West record, but it correctly identified that there were two 4/1/2012 records and ordered them properly.

Does anyone know how to adapt these formulas or have alternate formuals to achieve that I am looking for?

Also, if it is too complicated to get the formula to filter and sort in the same step, we could do this in two steps.  One set of formulas to bring a filtered list (could use my first example from the youtube video which does that fine).  Another set of formulas sorts the already filtered list, and handles the duplicate date rows properly.
jblahaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Easier if you want to get help to have a workbook with the above data posted so we can follow. Or else this means that someone need to copy paste all the info you have put in here which makes the possibility for you to get an answer very slim.
gowflow
0
gowflowCommented:
Is this what you are looking for ??

Load this file and make sure you activate macros.

Here is how it works. I have put a sample of the data there you can add as much as you want it updates automatically.

In E1 when you click on it there is a dropdown with all unique dates and in G1 the same thing when you select 2 dates it will automatically filter the data and sort it.

Notice that if you put from date bigger than to date it will erase the from date as impossible and will display all the data and same if you put to date smaller than from date it will also erase the to date and display all data.

Pls let me know your comments.
gowflow
Filter-Data.xlsm
0
jblahaAuthor Commented:
Taking your suggestion to upload a file to work with.  Here is an excel file calculating exactly as demonstrated in the youtube video.  It just needs to come back sorted by date, and then region instead of the way it is.
Ex894an.xlsx
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

gowflowCommented:
did you see the solution I posted ??? maybe our posts crossed
gowflow
0
jblahaAuthor Commented:
gowflow, our posts did cross.  Appreciate the effort, but I don't want to have any macros operate in this file (or have to use any manual filter or sort commands).  Please take a look at the example file, it works fairly well, but just doesn't bring the data back sorted as I would like.  Any ideas how to get this to work?
Thanks
0
barry houdiniCommented:
You can try this approach to give you all the dates in the correct order and then show the associated regions as per table order (you could use a formula to sort ascending by region too but that will add a level of complexity). I assume there won't be duplicate rows with the same date/region combination.

In E7

=IF(ROWS(E$7:E7)>$G$4,"",SMALL(IF($A$4:$A$13>=$E$4,IF($A$4:$A$13<=$F$4,$A$4:$A$13)),ROWS(E$7:E7)))

in F7

=IF(E7="","",INDEX(B$4:B$13,SMALL(IF($A$4:$A$13>=E7,ROW($A$4:$A$13)-ROW($A$4)+1),COUNTIF(E$7:E7,E7))))

in G7

=IF(E7="","",INDEX(C$4:C$13,MATCH(1,($A$4:$A$13=E7)*($B$4:$B$13=F7),0)))

all confirmed with CTRL+SHIFT+ENTER and copied down columns - see attached

regards, barry
Ex894an-barry.xlsx
0
jblahaAuthor Commented:
Barry,
I looked at the spreadsheet.  It does bring the dates sorted properly, but it doesn't bring the associated columns correctly (except for the exact order the spreadsheet was already in).  If I change row 10 to:

4/1/2012      East      $65.00

The results do expand by one row, and do show 3 rows all starting with 4/1/2012, but the other columns aren't bringing the correct data as follows:

4/1/2012      West      $23.00
4/1/2012      MidWest      $43.00
4/1/2012      West      $23.00
4/2/2012      MidWest      $43.00
4/3/2012      West      $11.00
4/4/2012      West      $44.00

So the 1st and 2nd result appear to be the 1st and 3rd occurrence 4/1/2012 in the original table, but the 3rd result appears to be the 1st occurrence from the original table again.  Also if I change the 13th row to:

4/1/2012      South      $43.00

The results are:
4/1/2012      West      $23.00
4/1/2012      MidWest      #N/A
4/1/2012      West      $23.00
4/2/2012      MidWest      $43.00
4/3/2012      West      $11.00
4/4/2012      West      $44.00

So this solution doesn't quite get us to the goal line.  Any other ideas?  I know this is a tough one, or I wouldn't have had to ask for help...
0
jblahaAuthor Commented:
I found another potential solution which gets me closer than my original posted example spreadsheet.  This one actually appears to filter the list properly, and also sorts the list by date and brings the proper associated data from the sorted filtered rows.

What this version is missing:

1)  Only sorts in Descending Order by date, would like to sort ascending instead.
2)  Does not sort the duplicate date rows by the region in ascending order.

I think this is the right direction, but requires a helper column which is fine but not ideal.  Any idea how to make this version meet the two remaining criteria?

Again, I'd like to do this in one set of formulas, but would take an additional step where the results in the attached are then the input table to another set of formulas.
FilteredSortedDescending.xlsx
0
gowflowCommented:
Is this what your looking for ? Was not clear that you 'didn't want a VBA solution' anyway check out this.
gowflow
FilteredSortedDescending.xlsx
0
barry houdiniCommented:
Hello jblaha,

I didn't go for the full sorted option in my previous answer, and as I said it wouldn't cope with Date/Region duplicates as you found.

I have now revised the formulas so that it does what you want, I think [see attached].

Entries are "sorted" (by use of the formulas only) ascending by Date and then ascending by Region and then ascending by Sales

Formulas in E7, F7 and G7 respectively:

=IF(ROWS(E$7:E7)>G$4,"",SMALL(IF(A$4:A$13>=E$4,IF(A$4:A$13<=F$4,A$4:A$13)),ROWS(E$7:E7)))

=IF(E7="","",INDEX(B$4:B$13,MATCH(SMALL(IF(A$4:A$13=E7,COUNTIFS(B$4:B$13,"<="&B$4:B$13)),COUNTIF(E$7:E7,E7)),IF(A$4:A$13=E7,COUNTIFS(B$4:B$13,"<="&B$4:B$13)),0)))

=IF(E7="","",SMALL(IF(A$4:A$13=E7,IF(B$4:B$13=F7,C$4:C$13)),COUNTIFS(E$7:E7,E7,F$7:F7,F7)))

All confirmed with CTRL+SHIFT+ENTER and copied down columns

regards, barry
Ex894an-barry2.xlsx
0
jblahaAuthor Commented:
gowflow,

Thanks for the spreadsheet.  Looks like you were able to make some progress on the example sheet I provided in my ID: 39589768 post:

Problems solved:

-Problem (1) from my ID: 39589768 post and example spreadsheet

Problems remaining that still need to be solved:

-Problem (2) from my ID: 39589768 post and example spreadsheet (secondarily sort the rows with duplicate dates on the Region in ascending order.)  (see attached).  I think there should be some way to make the Extract? column do this multilevel sorting instead of just adding a single digit to duplicates in the first column in the order they appear in the table.

What I like about the direction of this solution:

-The formulas in each of the result columns are the same except for the relative movement to select data accross the various columns.  This makes it easy to copy over to additional columns and retrieve data from more columns in the original table (see attached)

What I don't like about the direction of this solution:

-Requires the column labeled "Extract?" to do all the thinking to determine what to bring and in what order.  However this is perfectly acceptable, reduces complexity in the actual retrieving columns, and is growing on me.
FilteredSortedDescending-goflow-.xlsx
0
jblahaAuthor Commented:
Barry,

Good work.  Looks like you were able to make some progress on the original example sheet I provided in my ID: 39589106 post:

Problems solved:

-Problem (1) from my ID: 39589768 post and example spreadsheet
-Problem (2) from my ID: 39589768 post and example spreadsheet

New problems created:

-I am not sure what I should be placing in additional columns in to the right of the current result to bring data from additional columns in the original table. These additional columns don't need to be sorted any more than what we have already done, just data brought over for the appropriate rows (see attached).

What I like about the direction of this solution:

-Everything is computed in the actual result formulas, with no helper columns.
-The ability to sort on second and third factors if necessary.  This is nice!

What I don't like about the direction of this solution:

-The formulas in each columns are fundamentally different, which makes it difficult to add additional columns from the original table by copying accross.  But perhaps this isn't too bad if you can show me how to retrieve from additional columns that don't need to be further sorted and can be applied to all the retrieval columns to the right of the last one provided, and all of those formulas are the same.  That way the sorting columns are unique and I have to pay attention to those, but the additonal columns are all just copy over without much care needing to be paid.

Perhaps if the formula in I7 (your orignial G7) used INDEX() and the SMALL() function being called there could return the position of the Nth small result.  Then that formula could potentially be copied to all the columsn to the right to bring all the rest of the data.  Just a thought, not sure how to make it happen...
Ex894an-barry2-edited.xlsx
0
barry houdiniCommented:
OK, I see what you mean - I needed to make the formulas different for the sorting.....but assuming you don't really care about sorting the Sales column then just change I7 to this formula

=IF($G7="","",INDEX(C$4:C$13,SMALL(IF($A$4:$A$13=$G7,IF($B$4:$B$13=$H7,ROW(C$4:C$13)-ROW(C$4)+1)),COUNTIFS($G$7:$G7,$G7,$H$7:$H7,$H7))))

confirm with CTRL+SHIFT+ENTER

That can be copied across as far as required to retrieve subsequent column data, see attached

If you wanted the sorting for the Sales too then you can simply start that process further over. Post back and I'll give you an example of that too, if you require it

regards, barry
Ex894an-barry3.xlsx
0
barry houdiniCommented:
.....and I looked at gowflow's approach - that's probably the best way to go if you want to use formulas - it makes it much more efficient to get the position once rather than recalculate that every column - good work, gowflow!

If you don't mind I'd suggest a revised formula for the extract column which would give you sorting by date and then by region (otherwise in table order), i.e. this formula in F5 in your test sheet

=IF(AND(A5>=I$2,A5<=I$3),COUNTIFS(A$5:A$14,">="&I$2,A$5:A$14,"<="&I$3,A$5:A$14,"<"&A5)+COUNTIFS(A$5:A$14,">="&I$2,A$5:A$14,"<="&I$3,A$5:A$14,A5,B$5:B$14,"<"&B5)+COUNTIFS(A$5:A5,A5,B$5:B5,B5),"")

regards, barry
0
jblahaAuthor Commented:
Hi Barry,

This is really good!  If you can provide a solution to still let me sort by the sales before just retrieving the rest of the columns data, that would be great.

On this topic:  Are the formulas in the "sorting" columns dependent on:

Date = A date or numeric
Region = Text
Sales = numberic

I am asking because the ultimate robust solution that I could use over and over would be:

-A multi-input filter (like between two dates like we have)
-3 levels of sort that don't depend on whether the data is text or numberic
-Then bring all the other columns over associated with that 3-level sort

Thanks again for the help!!
0
jblahaAuthor Commented:
Barry, Just saw your post on goflow's path.  Can you adapt to have sort for the third column, and would these columns be format dependent (numeric, text, numeric)?
0
gowflowCommented:
did you try the one I posted ?
gowflow
0
gowflowCommented:
oops just saw your post seems my tennis got me way backlogged !!
gowflow
0
jblahaAuthor Commented:
To partially answer my quesiton from ID: 39591794 on data type dependency for the formula (in the goflow path with Barry's edit):

For the filtered column:
-The filter can be changed from a date to text and still work.  For example fill Column A with letters and then change the criteria boxes to a Lower and Upper letter, and the results still filter between those letters, and properly order the data based on this filter sort.  This is pretty awesome.

For the second sort column:
-If the rows with duplicates from the first sort are all text, or all numbers (within that set of duplicates only), then the secondary sort also still works.  For example, if there are 3 duplicates in the first column with all numeric second column rows, the sort works.  If there are an additional 2 duplicates in the first column with all text second columns, the sort still works.  So mixed data second column still sorts properly as long as all the data for each block of duplicates is the same type.  If the data is mixed within a block, it brings strange results.  I think this is OK, since unlikly to have mixed data within a block in a sort column.

Third sort column:
Would still like to have that solution.

Thanks!!
0
barry houdiniCommented:
>Barry, Just saw your post on goflow's path.  Can you adapt to have sort for the third column, and would these columns be format dependent (numeric, text, numeric)?

You can use this formula in F5 for a 3 column sort (by date, then region then sales, all ascending)

=IF(AND(A5>=I$2,A5<=I$3),COUNTIFS(A$5:A$14,">="&I$2,A$5:A$14,"<="&I$3,A$5:A$14,"<"&A5)+COUNTIFS(A$5:A$14,">="&I$2,A$5:A$14,"<="&I$3,A$5:A$14,A5,B$5:B$14,"<"&B5)+COUNTIFS(A$5:A$14,">="&I$2,A$5:A$14,"<="&I$3,A$5:A$14,A5,B$5:B$14,B5,C$5:C$14,"<"&C5)+COUNTIFS(A$5:A5,A5,B$5:B5,B5,C$5:C5,C5),"")

I think, by definition, the first column format would need to be numeric (as you are looking for a value between two others, unless you want to apply that to text values?) but the 2nd and 3rd columns can be numeric or text it shouldn't matter which

regards, barry
0
jblahaAuthor Commented:
Thank you both very much!!!!

Ok so the goflow path with the Barry edits does exactly what I want and is very robust (see attached).  what do I do about choosing a solution and awarding the points?
FilteredSortedDescending-goflow-.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jblahaAuthor Commented:
The ultimate solution was based on a path that started with a partial solution that I uploaded, was then tweaked by gowflow, then made better and ultimately perfected by Barry.  So Barry got most of the points, and goflow the rest.  I just selected my answer as the best because it has an actual copy of the spreadsheet that ultimately provides the solution that might be helpful to someone looking for the final answer, but the credit should go to Barry & goflow for all their excellent help on this complicated problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.