Solved

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

Posted on 2013-10-21
Medium Priority
334 Views
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:
Actual spreadsheet for this is here (tab=894an):

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.
0
Question by:jblaha
• 11
• 6
• 5

LVL 31

Expert Comment

ID: 39588217
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

LVL 31

Expert Comment

ID: 39589080
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.

gowflow
Filter-Data.xlsm
0

Author Comment

ID: 39589106
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

LVL 31

Expert Comment

ID: 39589121
did you see the solution I posted ??? maybe our posts crossed
gowflow
0

Author Comment

ID: 39589137
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

LVL 50

Expert Comment

ID: 39589563
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

Author Comment

ID: 39589711
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

Author Comment

ID: 39589768
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

LVL 31

Assisted Solution

gowflow earned 400 total points
ID: 39590201
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

LVL 50

Expert Comment

ID: 39590513
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

Author Comment

ID: 39591276
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

Author Comment

ID: 39591326
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

LVL 50

Expert Comment

ID: 39591634
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

LVL 50

Expert Comment

ID: 39591758
.....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

Author Comment

ID: 39591782
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

Author Comment

ID: 39591794
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

LVL 31

Expert Comment

ID: 39591847
did you try the one I posted ?
gowflow
0

LVL 31

Expert Comment

ID: 39591851
oops just saw your post seems my tennis got me way backlogged !!
gowflow
0

Author Comment

ID: 39591855
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

LVL 50

Assisted Solution

barry houdini earned 1600 total points
ID: 39591937
>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

Accepted Solution

jblaha earned 0 total points
ID: 39591971
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

Author Closing Comment

ID: 39603627
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

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month13 days, 2 hours left to enroll

#### 607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.