Formula to Extract Rows and Reorder in new Table

[M10Ant]

I have the below table:

I'm struggling to work out a formula I can use to both filter and re-order the data in another table.

Specifically for the other table to show only those rows that have "Y" as the "Web Order?" value and for the rows to be sorted in ascending order of the "Order Dispatched Date"

I know it's easy to accomplish this in Excel 365 using the new FILTER and SORT functions, but this will give me a dynamic spill array which is something that can't exist inside a table, and a table is needed for the reorganised data (additional columns will be added later to the target table).

I also need for the formula to take into account the possibility of duplicate Order Dispatch Date values on different rows in the source table.

I can see the formula potentially making use of COUNTIF on the "Order Dispatched Date?" column in conjunction with SMALL to determine the current row's relative order position, and use that as the row argument in an INDEX function, but struggling to see how to do this whilst also just filtering on those rows where "Web Order?" = "Y".

The below formula works fine if I just needed the new table to filter the data on "Web Order?" = "Y":

Formula for Order Placed column on new table:
=IFERROR(INDEX(tblSrc6,SMALL(IF(tblSrc6[Web Order?]="Y",ROW(tblSrc6)),ROW(1:1))-(ROW(tblSrc6[[#Headers],[Web Order?]])),1),"")

Formula for Customer column on new table:
=IFERROR(INDEX(tblSrc6,SMALL(IF(tblSrc6[Web Order?]="Y",ROW(tblSrc6)),ROW(1:1))-(ROW(tblSrc6[[#Headers],[Web Order?]])),2),"")

I just can't get my head around how to also have the formula also reorder the filtered data on "Order Dispatched Date?".

Grateful for any help!

 

[jasonb75]

Pull the dates first using something like

= AGGREGATE(15,6, tblSrc6/(tblSrc6[Web Order?]="Y"),ROWS(Z$2:Z2))

Where Z$2:Z2 refers to the cell where you enter the first formula before filling down. The same references in the second formula below need to point to this cell as well.

Next use an index based formula like this one to get the order placed details, use similar for any other columns to extract from the source table.

=IFERROR(INDEX(tblSrc6[Order Placed],AGGREGATE(15,6,ROW(tblSrc6[Order Placed)/(tblSrc6[Web Order?]="Y")/(tblSrc6[Order Despatched Date]=Z2),COUNTIF(Z$2:Z2,Z2))-tblSrc6[[#Headers],[Web Order?]]),"")

I've just typed the formulas on my phone, hopefully they're correct but if either of them show errors, check the spelling of table names or missing parentheses.

 

[M10Ant]

Hi Jason

Pull the dates first using something like

= AGGREGATE(15,6, tblSrc6/(tblSrc6[Web Order?]="Y"),ROWS(Z$2:Z2))

Where Z$2:Z2 refers to the cell where you enter the first formula before filling down. The same references in the second formula below need to point to this cell as well.

Next use an index based formula like this one to get the order placed details, use similar for any other columns to extract from the source table.

=IFERROR(INDEX(tblSrc6[Order Placed],AGGREGATE(15,6,ROW(tblSrc6[Order Placed)/(tblSrc6[Web Order?]="Y")/(tblSrc6[Order Despatched Date]=Z2),COUNTIF(Z$2:Z2,Z2))-tblSrc6[[#Headers],[Web Order?]]),"")

I've just typed the formulas on my phone, hopefully they're correct but if either of them show errors, check the spelling of table names or missing parentheses.

Hi Jason

Thank you very much for this. That looks exactly like the kind of formula I need!

When I input it, Excel is showing errors re. missing parentheses as you say.
I just can't work out all the ones to add unfortunately (very much a Excel beginner):

I firstly changed
=IFERROR(INDEX(tblSrc6[Order Placed],AGGREGATE(15,6,ROW(tblSrc6[Order Placed)/
To:
=IFERROR(INDEX(tblSrc6[Order Placed],AGGREGATE(15,6,ROW(tblSrc6[Order Placed])

But see Excel is flagging a syntax error elsewhere now. I can't see where the ROW function should end.

Assuming the table on the right below is where are need the filtered/reordered data to appear, I'd be grateful if you could provide the formula I would need in each of the two columns (I couldn't work out where you are selecting the relevant column in the INDEX formula above). Again, just a lack of sufficient Excel understanding on my part at the moment.

 

[M10Ant]

I've added the formula to the new table's Order Dispatch Date and gotten rid of the syntax errors. I can see it must be triggering the IFERROR part currently (if I add text within the last ""), that's the text I see in the column. Pretty sure it's because I'm not adjusting the formula as needs be for the new table columns. I've extracted the Xl2bb below if that helps:

Excel-Cheat-Sheet.xlsx
	A	B	C	D	E	F	G	H
1
2		Order Placed	Customer	Order Dispatched Date	Web Order?		Order Dispatched Date	Customer
3		01/05/2022 15:00	John Simpson	03/06/2022	Y
4		17/06/2022 08:45	Carol Walker	24/06/2022	N
5		11/05/2022 16:20	Jason Millbank	12/05/2022	Y
6		22/06/2022 12:10	Steven Jones	24/06/2022	Y
7		07/05/2022 15:55	Paul Cragg	14/05/2022	N
8		16/07/2022 10:23	Evan Tompson	19/05/2022	Y
9		10/05/2022 20:20	Bill Green	11/05/2022	Y
10		19/07/2022 21:10	Henry Down	21/07/2022	Y
11		19/07/2022 21:10	Katherine Day	27/07/2022	N
12		05/05/2022 16:08	Sue Evans	08/06/2022	Y
6

Cell Formulas
Range	Formula
G3	G3	=IFERROR(INDEX(tblSrc6[Order Placed],AGGREGATE(15,6,ROW(tblSrc6[Order Placed])/(tblSrc6[Web Order?]="Y")/(tblSrc6[Order Dispatched Date]=Z3),COUNTIF(Z3:Z$3,Z3))-tblSrc6[[#Headers],[Web Order?]]),"")
G4:G12	G4	=IFERROR(INDEX(tblSrc6[Order Placed],AGGREGATE(15,6,ROW(tblSrc6[Order Placed])/(tblSrc6[Web Order?]="Y")/(tblSrc6[Order Dispatched Date]=Z4),COUNTIF(Z$3:Z4,Z4))-tblSrc6[[#Headers],[Web Order?]]),"")

 

[jasonb75]

Sorry, didn't get chance to look at this again earlier. I've attached an XL2BB table with the correct formulas, hopefully this gets you going.

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

Cell Formulas
Range	Formula
G3:G12	G3	=IFERROR(AGGREGATE(15,6,tblSrc6[Order Dispatched Date]/(tblSrc6[Web Order?]="Y"),ROWS(G$3:G3)),"")
H3:H12	H3	=IFERROR(INDEX(tblSrc6[Customer],AGGREGATE(15,6,ROW(tblSrc6[Customer])/(tblSrc6[Web Order?]="Y")/(tblSrc6[Order Dispatched Date]=G3),COUNTIF(G$3:G3,G3))-ROW(tblSrc6[[#Headers],[Web Order?]])),"")

 

[M10Ant]

Sorry, didn't get chance to look at this again earlier. I've attached an XL2BB table with the correct formulas, hopefully this gets you going.

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

Cell Formulas
Range	Formula
G3:G12	G3	=IFERROR(AGGREGATE(15,6,tblSrc6[Order Dispatched Date]/(tblSrc6[Web Order?]="Y"),ROWS(G$3:G3)),"")
H3:H12	H3	=IFERROR(INDEX(tblSrc6[Customer],AGGREGATE(15,6,ROW(tblSrc6[Customer])/(tblSrc6[Web Order?]="Y")/(tblSrc6[Order Dispatched Date]=G3),COUNTIF(G$3:G3,G3))-ROW(tblSrc6[[#Headers],[Web Order?]])),"")

Thank you very much Jason That is exactly what I need! It would have have taken me months to work that out - if at all. Extremely grateful for this.