Single Formula to Extract Rows and Reorder in New Table v2

[M10Ant]

I have a source Order Table that records the date customers placed an order, when the order was dispatched and whether the order was a web order or not. This is shown in the left-most column below.

My aim is to have a report table that shows only all Web Orders, sorting in ascending order of the Order Dispatched date and listing the customer name. This is shown in the middle table below.

Excel-Cheat-Sheet.xlsx
	B	C	D	E	F	G	H	I	J	K	L
8	Source Order Table Data					Report Table v1			Report Table v2
9	Order Placed	Customer	Order Dispatched	Web Order?		Order Dispatched	Customer		Source ROW #	Order Dispatched	Customer
10	01/05/2022 15:00	John Simpson	03/06/2022	Y		11/05/2022	Bill Green		7	11/05/2022	Bill Green
11	17/06/2022 08:45	Carol Walker	24/06/2022	N		12/05/2022	Jason Millbank		3	12/05/2022	Jason Millbank
12	11/05/2022 16:20	Jason Millbank	12/05/2022	Y		19/05/2022	Evan Tompson		6	19/05/2022	Evan Tompson
13	22/06/2022 12:10	Steven Jones	24/06/2022	Y		03/06/2022	John Simpson		1	03/06/2022	John Simpson
14	07/05/2022 15:55	Paul Cragg	14/05/2022	N		08/06/2022	Sue Evans		10	08/06/2022	Sue Evans
15	16/07/2022 10:23	Evan Tompson	19/05/2022	Y		08/06/2022	John Deen		11	08/06/2022	John Deen
16	10/05/2022 20:20	Bill Green	11/05/2022	Y		24/06/2022	Steven Jones		4	24/06/2022	Steven Jones
17	19/07/2022 21:10	Henry Down	21/07/2022	Y		21/07/2022	Henry Down		8	21/07/2022	Henry Down
18	19/07/2022 21:10	Katherine Day	27/07/2022	N
19	05/05/2022 16:08	Sue Evans	08/06/2022	Y
20	06/06/2022 11:05	John Deen	08/06/2022	Y
7 (2)

Cell Formulas
Range	Formula
G10:G20,K10:K20	G10	=IFERROR(AGGREGATE(15,6,tblSrcOrders[Order Dispatched]/(tblSrcOrders[Web Order?]="Y"),ROW([@[Order Dispatched]])-ROW(tblReportv1[[#Headers],[Order Dispatched]])),"")
H10:H20	H10	=IFERROR(INDEX(tblSrcOrders[Customer],AGGREGATE(15,6,ROW(tblSrcOrders[Customer])/(tblSrcOrders[Web Order?]="Y")/(tblSrcOrders[Order Dispatched]=G10),COUNTIF(G$10:G10,G10))-ROW(tblSrcOrders[[#Headers],[Web Order?]])),"")
J10:J20	J10	=IF(ROWS(tblReportv2[[#Headers],[Source ROW '#]]:[@[Source ROW '#]])-1<=(COUNTIF(tblSrcOrders[Web Order?],"Y")), IFERROR(AGGREGATE(15,6,ROW(tblSrcOrders[Customer])/(tblSrcOrders[Web Order?]="Y")/(tblSrcOrders[Order Dispatched]=[@[Order Dispatched]]),COUNTIF(tblReportv2[[#Headers],[Order Dispatched]]:[@[Order Dispatched]],[@[Order Dispatched]]))-ROW(tblSrcOrders[[#Headers],[Web Order?]]),""),"")
L10:L20	L10	=IF([@[Source ROW '#]]<>"", INDEX(tblSrcOrders,[@[Source ROW '#]],2), "")

Named Ranges
Name	Refers To	Cells
'7 (2)'!tblRptHelper	='7 (2)'!$J$10	L10, J11:J20

Firstly a big thank you to Jasonb75 who provided me with the formulas to use in the report table which provides the data needed and is a brilliant solution. (It also took my hours to understand being such an Excel beginner!). Link to original post here: Formula to Extract Rows and Reorder in new Table

The table in the middle above relies on the formula of two columns to get the row positions of all relevant rows, and sort in ascending order of Order Dispatched dates. Referring to the columns in that middle reporting table:

• Order Dispatched: Calculates the array of relevant dates, ordered in ascending order and including all duplicate dates where they exist, and pulls out the array entry relevant for the current row
• Customer: Uses the calculated Order Dispatched date in the report table to return the relevant Customer entry corresponding to it from the source table and also takes into account duplicate dates

The solution works great, but intending to use this on a larger data set with more columns to report on, I'm trying to see if there is a single formula I can use in a "helper" column at the start of the report table that will return the row number of the relevant row from the source table column in the report table. This would enable me to use a simple =INDEX(range, row_num) formula on any columns I need to include in the report table, using the calculated row number in the Helper column in the row_num argument to return the relevant entry.

The third table above gives an idea of what I'm aiming for. Whilst the table still provides the required data, the issue is that the helper column still relies on the formula in another column to get the row positions (Order Dispatched column in that same report table). I'm hoping there is a single formula I can use in the helper column to do this. Most likely somehow a combination of the two formulas.

 

[M10Ant]

Hi Eric

Thanks for the explanation. It's an ingenious solution! I see the SORT function also ensures duplicates dates are not missed out, removing the need for using the SMALL function to cycle through duplicate dates and take them into consideration. So pleased this has gotten me over a big hurdle in my spreadsheet and can now progress further with it. Thanks very much for your time helping me with this Eric.

 

[M10Ant]

Mainly for my own benefit and to help me understand it, I've gone through and elaborated on Eric's explanation of SOURCE ROW formula. Just in case it helps anyone else.

[SOURCE ROW] value is calculated as follows:

SEQUENCE(ROWS(tblSrcOrders)) returns an array of numbers starting and one and incrementing by 1 for as many rows as there are in the source table. This effectively assigns each row in the table a unique row number.

tblSrcOrders[Dispatched] returns an array consisting of the [Dispatched] value of each row in the source table.

Both of these one-dimensional (1-column) arrays are then combined together into a two-dimensional array via CHOOSE({1,2},...)

The FILTER function is then applied to this two-dimensional array to filter all rows where [Web Order?] = "Y"

FILTER(...,tblSrcOrders[Web Order?]="Y")

This filtered two-dimensional array now effectively identifies the row numbers of each row in the source table where [Web Order?] = "Y", and lists the dispatch date associated with each. This filtered two-dimensional array then forms the array of the SORT function to sort the rows in ascending order. No parameters are specified for the SORT function other than the mandatory array. By default, the function will sort based on the contents of the first column of the array, which is the [Dispatched] date values, and in ascending order by default.

SORT(...)

The sorted, filtered array (which is still a two-dimensional array consisting of dispatch dates and their corresponding row numbers) then forms the array argument of an INDEX function.

INDEX(...,ROW([@[Source ROW]])-MIN(ROW([Source ROW]))+1,2)

This INDEX function serves to pull out the row numbers from the sorted, filtered two-dimensional array, starting at the first entry, and progressing through, one by done, for each successive row in the report table.

The calculation for the INDEX function's row_num argument is ROW([@[Source ROW]])-MIN(ROW([Source ROW]))+1. This returns a value starting at one and incrementing by one for each successive row in the report table. This ensures the INDEX function will return values from each successive row in the sorted, filtered array. The ROW([@[Source ROW]]) by itself will return the spreadsheet row number for the current row in the report table. To make the values start at one instead, the row number of the first data row of the source table is subtracted from this value and then 1 is added back on:
-MIN(ROW([Source ROW]))+1