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!
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!