Formula to Extract Rows and Reorder in new Table

M

M10Ant

New Member
Joined
Dec 30, 2021
Messages
36
Office Version
  1. 365
Platform
  1. Windows
I have the below table:
MrExcel-Query.png


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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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.
 
Upvote 0
Hi Jason
jasonb75 said:
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.
Click to expand...
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.
MrExcel-Query-screenshot2.png
 
Upvote 0
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
ABCDEFGH
1
2Order PlacedCustomerOrder Dispatched DateWeb Order?Order Dispatched DateCustomer
301/05/2022 15:00John Simpson03/06/2022Y 
417/06/2022 08:45Carol Walker24/06/2022N 
511/05/2022 16:20Jason Millbank12/05/2022Y 
622/06/2022 12:10Steven Jones24/06/2022Y 
707/05/2022 15:55Paul Cragg14/05/2022N 
816/07/2022 10:23Evan Tompson19/05/2022Y 
910/05/2022 20:20Bill Green11/05/2022Y 
1019/07/2022 21:10Henry Down21/07/2022Y 
1119/07/2022 21:10Katherine Day27/07/2022N 
1205/05/2022 16:08Sue Evans08/06/2022Y 
6
Cell Formulas
RangeFormula
G3G3=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:G12G4=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?]]),"")
 
Upvote 0
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
BCDEFGH
2Order PlacedCustomerOrder Dispatched DateWeb Order?Order Dispatched DateCustomer
301/05/2022 15:00John Simpson03/06/2022Y11/05/2022Bill Green
417/06/2022 08:45Carol Walker24/06/2022N12/05/2022Jason Millbank
511/05/2022 16:20Jason Millbank12/05/2022Y19/05/2022Evan Tompson
622/06/2022 12:10Steven Jones24/06/2022Y03/06/2022John Simpson
707/05/2022 15:55Paul Cragg14/05/2022N08/06/2022Sue Evans
816/07/2022 10:23Evan Tompson19/05/2022Y24/06/2022Steven Jones
910/05/2022 20:20Bill Green11/05/2022Y21/07/2022Henry Down
1019/07/2022 21:10Henry Down21/07/2022Y  
1119/07/2022 21:10Katherine Day27/07/2022N  
1205/05/2022 16:08Sue Evans08/06/2022Y  
Sheet1
Cell Formulas
RangeFormula
G3:G12G3=IFERROR(AGGREGATE(15,6,tblSrc6[Order Dispatched Date]/(tblSrc6[Web Order?]="Y"),ROWS(G$3:G3)),"")
H3:H12H3=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?]])),"")
 
Upvote 0
Solution
jasonb75 said:
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
BCDEFGH
2Order PlacedCustomerOrder Dispatched DateWeb Order?Order Dispatched DateCustomer
301/05/2022 15:00John Simpson03/06/2022Y11/05/2022Bill Green
417/06/2022 08:45Carol Walker24/06/2022N12/05/2022Jason Millbank
511/05/2022 16:20Jason Millbank12/05/2022Y19/05/2022Evan Tompson
622/06/2022 12:10Steven Jones24/06/2022Y03/06/2022John Simpson
707/05/2022 15:55Paul Cragg14/05/2022N08/06/2022Sue Evans
816/07/2022 10:23Evan Tompson19/05/2022Y24/06/2022Steven Jones
910/05/2022 20:20Bill Green11/05/2022Y21/07/2022Henry Down
1019/07/2022 21:10Henry Down21/07/2022Y  
1119/07/2022 21:10Katherine Day27/07/2022N  
1205/05/2022 16:08Sue Evans08/06/2022Y  
Sheet1
Cell Formulas
RangeFormula
G3:G12G3=IFERROR(AGGREGATE(15,6,tblSrc6[Order Dispatched Date]/(tblSrc6[Web Order?]="Y"),ROWS(G$3:G3)),"")
H3:H12H3=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?]])),"")
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Dynamic (row) selection in index formula based on date selection
2
Replies
10
Views
492
DanteAmor
DanteAmor
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
159
datatronics505
datatronics505
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
441
Anthony47
A
drom
  • Question Question
pivot table SORT in data source order
Replies
1
Views
903
Misca
M
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
548
felixstraube
F

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top