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.

 

[Eric W]

I suspect Jason must not have the most recent version of Excel that has the dynamic array functions. You should have them in Excel 365. It's easier with them (although still not quite a snap):

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

Cell Formulas
Range	Formula
G10:H17	G10	=LET(s,SORT(FILTER(tblSrcOrders,tblSrcOrders[Web Order?]="Y"),3),CHOOSE({1,2},INDEX(s,0,3),INDEX(s,0,2)))
J10:L17	J10	=LET(a,CHOOSE({1,2,3},SEQUENCE(ROWS(tblSrcOrders)),tblSrcOrders[Order Dispatched],tblSrcOrders[Customer]),f,FILTER(a,tblSrcOrders[Web Order?]="Y"),SORT(f,2))
Dynamic array formulas.

No formatting on the output tables, but that can be done with Conditional Formatting.

 

[M10Ant]

Hi Eric

Thank you for the reply and the information. Sorry I should have said I’m hoping for a solution that doesn’t use dynamic arrays (need the filtered/reordered data to exist in a table).

 

[Eric W]

Is there a reason you need a table? We can use Conditional Formatting to imitate the banding, and we can use INDEX(J10#,0,SourceRow) in formulas instead of the usual table nomenclature of tblTableTwo[Source Row #].

 

[M10Ant]

Hi Eric

Yes a table is needed unfortunately as I'll be tagging on lots of additional helper calculation columns onto the far right of the report table that rely on the filtered/re-ordered data pulled into it from the source table. The data set is much bigger than listed above, but didn't want to detract from the main goal above. If there is a single non-dynamic array formula that can be used in the report table's 'Source ROW #' column, that when copied down, will list the relevant filtered row numbers and in the correct order, I'll be able to sort the rest. I'm just not sure if it is possible to do with a single formula.

I can see the formula needing to first create an array of all Ordered Dispatched dates where "Web Order" = "Y", with the list being in ascending ordered of those dates (also ensuring that array includes all duplicate date entries with "Web Order" = "Y"), and then cycle through that array to return the row number of those rows.

The first step is done nicely by the AGGREGATE function's inbuilt SMALL function (Report Table v1's "Order Dispatched" column) in original example above. I just can't work out how to combine this with the second step in a single formula.

 

[Eric W]

OK, try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5
6
7
8		Source Order Table Data
9		Order Placed	Customer	Order Dispatched	Web Order?			Source Row	Order Dispatched	Customer
10		5/1/22 15:00	John Simpson	6/3/2022	Y			7	5/11/2022	Bill Green
11		6/17/22 8:45	Carol Walker	6/24/2022	N			3	5/12/2022	Jason Millbank
12		5/11/22 16:20	Jason Millbank	5/12/2022	Y			6	5/19/2022	Evan Tompson
13		6/22/22 12:10	Steven Jones	6/24/2022	Y			1	6/3/2022	John Simpson
14		5/7/22 15:55	Paul Cragg	5/14/2022	N			10	6/8/2022	Sue Evans
15		7/16/22 10:23	Evan Tompson	5/19/2022	Y			11	6/8/2022	John Deen
16		5/10/22 20:20	Bill Green	5/11/2022	Y			4	6/24/2022	Steven Jones
17		7/19/22 21:10	Henry Down	7/21/2022	Y			8	7/21/2022	Henry Down
18		7/19/22 21:10	Katherine Day	7/27/2022	N
19		5/5/22 16:08	Sue Evans	6/8/2022	Y
20		6/6/22 11:05	John Deen	6/8/2022	Y
21
Sheet12

Cell Formulas
Range	Formula
H10:H20	H10	=IFERROR(SMALL(FILTER(SEQUENCE(ROWS(tblSrcOrders)),(tblSrcOrders[Web Order?]="Y")*(tblSrcOrders[Order Dispatched]=[@[Order Dispatched]])),COUNTIF(INDEX([Order Dispatched],1):[@[Order Dispatched]],[@[Order Dispatched]])),"")
I10:I20	I10	=IFERROR(SMALL(FILTER(tblSrcOrders[Order Dispatched],tblSrcOrders[Web Order?]="Y"),ROW([@[Source Row]])-MIN(ROW(Table4))+1),"")
J10:J20	J10	=IFERROR(INDEX(tblSrcOrders[Customer],[@[Source Row]]),"")

 

[M10Ant]

Hi Eric

Thank you for this. I've just plugged the formulas into my table. I can confirm it does give me the expected table results. However, the formula in the Source Row column of the report table is still relying on the computed values in the Order Dispatched column in that same table.

I'm referring to the part in bold below:
=IFERROR(SMALL(FILTER(SEQUENCE(ROWS(tblSrcOrders)),(tblSrcOrders[Web Order?]="Y")*(tblSrcOrders[Order Dispatched]=[@[Order Dispatched]])),COUNTIF(INDEX([Order Dispatched],1):[@[Order Dispatched]],[@[Order Dispatched]])),"")

I can see the purpose of the COUNTIF function being to account for any duplicate Order Dispatch date entries in the source table, and ensure each duplicate date is returned one at a time as the SMALL function's "nth" argument.

I'm just wondering if there is a way for the Source ROW column to return the same expected values without a need to reference the Order Dispatched dates column in that same table. It would need to some how embed that date array within itself and use it in the COUNTIF function.

This would mean I could use a simple INDEX(range,row_num) formula on both the "Order Dispatched" and "Customer" columns in the report table, with both using the value from the Source ROW column as the row_num argument.

 

[Eric W]

OK, try:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5
6
7
8		Source Order Table Data
9		Order Placed	Customer	Order Dispatched	Web Order?			Source Row	Order Dispatched	Customer
10		5/1/22 15:00	John Simpson	6/3/2022	Y			7	5/11/2022	Bill Green
11		6/17/22 8:45	Carol Walker	6/24/2022	N			3	5/12/2022	Jason Millbank
12		5/11/22 16:20	Jason Millbank	5/12/2022	Y			6	5/19/2022	Evan Tompson
13		6/22/22 12:10	Steven Jones	6/24/2022	Y			1	6/3/2022	John Simpson
14		5/7/22 15:55	Paul Cragg	5/14/2022	N			10	6/8/2022	Sue Evans
15		7/16/22 10:23	Evan Tompson	5/19/2022	Y			11	6/8/2022	John Deen
16		5/10/22 20:20	Bill Green	5/11/2022	Y			4	6/24/2022	Steven Jones
17		7/19/22 21:10	Henry Down	7/21/2022	Y			8	7/21/2022	Henry Down
18		7/19/22 21:10	Katherine Day	7/27/2022	N
19		5/5/22 16:08	Sue Evans	6/8/2022	Y
20		6/6/22 11:05	John Deen	6/8/2022	Y
21
Sheet1

Cell Formulas
Range	Formula
H10:H20	H10	=IFERROR(INDEX(SORT(FILTER(CHOOSE({1,2},tblSrcOrders[Order Dispatched],SEQUENCE(ROWS(tblSrcOrders))),tblSrcOrders[Web Order?]="Y")),ROW([@[Source Row]])-MIN(ROW([Source Row]))+1,2),"")
I10:I20	I10	=IFERROR(INDEX(tblSrcOrders[Order Dispatched],[@[Source Row]]),"")
J10:J20	J10	=IFERROR(INDEX(tblSrcOrders[Customer],[@[Source Row]]),"")

 

[M10Ant]

Hi Eric

Thank you very much for this. That works brilliantly and is exactly what I need I didn't think it was even possible!

I'd be grateful if you could please give me a brief explanation of how the H10:H20 formula works. I've not come across the CHOOSE{1,2} function before and would be interested so see the purpose of it here.


Many Thanks
Anthony

 

[Eric W]

At its most basic, CHOOSE just lets you select an option based on a 1,2,3... value. CHOOSE(2,"A","B","C") would return "B" since it is the second value in the list. If you give it an array of options, it will return an array of results, so CHOOSE({1,2},"A","B","C") returns {"A","B"}. So in the H10 formula it returns an array of tblSrcOrders[Order Dispatched] and SEQUENCE(ROWS(tblSrcOrders)). Since each of those are 1-D arrays, you end up with a 2-D array, the first column being the date, and the second column the row number. (Microsoft is working on some new functions to merge arrays a little more neatly, but this works for now.)

With the 2-D array, the rest is pretty easy. The FILTER removes rows without a "Y", the SORT sorts it by date (the first column), then finally the INDEX gets the row number from the sorted 2-D array.