Dynamically Sorting Data by Two Variables (Date + Amount)

[datbigdog]

Trying to figure out a way to dynamically sort data (pulled from multiple tabs) by two variables: first by date based on a reference that includes Month/Year then by Amount in Descending order.

The data will be pulled in from multiple tabs and will be discontinuous given that each tab has 100 lines of input available for use and obviously not all the lines will be in use all the time.

Please see example spreadsheet attached. Trying to dynamically sort the information on the 'Summary' tab based on date (including all Projects that occur within the Month/Year reference in B3) then by amount in descending order. Is there a way to keep this sorting dynamic so as you add data the sorting still works?

[FlameRetired]

This should get you started.

In B8 filled down and across

Please Login or Register  to view this content.

Since all the dates are in the same month as B3 and already sorted it is hard for me to envision how this part would work

Trying to dynamically sort the information on the 'Summary' tab based on date (including all Projects that occur within the Month/Year reference in B3) then by amount in descending order. Is there a way to keep this sorting dynamic so as you add data the sorting still works?

Without a sample representative of that part I don't think I can help further. I am left to imagine and guess too much.

Edit Additionally most of us recommend setups that work in the opposite direction. Gather all the data (including a field for Input1, Input2, ... etc) into a flat database and then summarize in the relevant sheets. Summarizing data captured the other way is almost always going to be complicated and awkward ... not to mention a nightmare to trouble shoot and update.

[datbigdog]

I guess I am confused: is the 'Summary' tab not what you would consider a flat database? The end goal is to be able to organize in descending order the projects that have been completed by date and amount, with the projects' data sourcing from three separate tabs (representing different parts of a company's organization, but all falling under the same leader).

This has been built to track all projects, so the input tabs will be continually updated as more projects finalize. I am fine building in a holistically different summary tab if you feel thats a more efficient way, but end goal would be the above - sort projects by descending date (ideally by Month/Day) and amount.

Let me know if you need additional clarification.

[datbigdog]

Find an updated example spreadsheet attached, including an example of what an ideal output would look like on the 'Summary' tab. I would be fine utilizing a broken out area within the Summary tab like this if that makes it easier. Thanks again for your input.

[FlameRetired]

I guess I am confused: is the 'Summary' tab not what you would consider a flat database?

The idea I was trying to convey is that data capture in multiple sheets (treating each of them flat databases) and then trying to summarize in another sheet is awkward as well as inefficient and may require too many executions of the INDIRECT function. If you are not aware of it INDIRECT is one of the volatile functions. In a project the size of which yours suggests this could be a critical issue.

If you would like to study up on volatility and its importance try this article.

Decision Models

I failed to mention that what you seem to want to do could be done by keeping a single sheet that captures all data and then use filtering to do the desired summary ... or do the summary by formula in another area of that sheet or even in a second ('Summary') sheet.

How would you like to proceed?

Does this help?

[FlameRetired]

The attached is an example of what I propose ... flat database source/summary sheet.

Find all the data from all the 'Input#' sheets in the first upload copied/pasted in the 'Dbase' sheet. This would be the data capture sheet.

In 'Summary' sheet find these formulas summarizing the data by 1/1/2019.

Input#s

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Dates

Please Login or Register  to view this content.

This entered in C3 filled down and across returns Project and Amount.

Please Login or Register  to view this content.

Changing the query date in A1 changes the output to that month range.

I'm not sure what you have in mind for the date/amount sort in your last upload as the amounts are already sorted in the source and the Input field is missing.

[datbigdog]

I think I see what you were confused about now. In both of the workbook's I uploaded, I only included the 'Input 1', 'Input 2', and 'Input 3' to denote where the information was/is sourcing from. I don't need that included in the sort functionality at all. So the data would be the information from B7:D52. See updated example workbook

Does this change your solution at all? Or can I still pull the formula's you've provided in your workbook on the Summary tab excluding the reference to the input?

Thank you so much for your help thus far and apologies for any confusion

[Bo_Ry]

Is this what you want?

Summary G8 drag to I8 and drag down

Please Login or Register  to view this content.

or Just for Jan-19

Please Login or Register  to view this content.

[datbigdog]

Is there a way to make the bottom formula dynamic to include a cell reference that would change the month based on a selection by the user but keep the formula working?

[Bo_Ry]

Yes, Please see attached

Select month by key date at G6

=IFERROR(INDEX(B$8:B$52,MATCH(AGGREGATE(15,6,($B$8:$B$52*10^9-$D$8:$D$52)/($B$8:$B$52>0)/(TEXT($B$8:$B$52,"mmyy")=TEXT($G$6,"mmyy")),ROWS(G$8:G8)),INDEX($B$8:$B$52*10^9-$D$8:$D$52,),)),"")

[datbigdog]

Thank you! Repped.

[datbigdog]

Is there a way to add another test but keep the existing functionality? I was hoping to add a test (like the date test) that was driven by cell H7, where it would only pull in those projects for the specified 'Team' (i.e. VP1 in this case). Example below.

[alansidman]

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

[Bo_Ry]

Please try at H9 and copy over

Please Login or Register  to view this content.

Addition criteria is in blue

[datbigdog]

Bo_Ry (or anyone else) is there a way to order the information starting in the table in H9 by the 'Amount' field? Find that the sort right now works by date which is less important than amount. TIA

[Bo_Ry]

Please upload a sample of the expected result.

[datbigdog]

I'm trying to see if there is an attribute I can add to the formulas contained within H9:K13 that would organize the data like it is displayed in M9:P13. Thanks in advance.

[Bo_Ry]

Pleas try at H9:K13

=IFERROR(INDEX(B:B,MOD(AGGREGATE(14,6,$E$9:$E$53*10^3+ROW($D$9:$D$53)/(TEXT($B$9:$B$53,"mmmyy")=TEXT($H$6,"mmmyy"))/($D$9:$D$53=$H$7),ROWS(H$9:H9)),10^3)),"")