Hi All,
Be great if someone could help on this on.
I have main table of data that consists of a long list of products being manufactured. For each row (that represents one product), our delivery coordinator can choose day for delivery + vehicle for that day + delivery order. Sometimes more than one item my be delivered to the same customer so if the first two products are going to the one delivery address, the order entered under 'delivery order' column in this main table will be 1, 1 respectively for these two items. The next delivery may have one item being delivered so the coordinator would enter 2 and so on.
I then have another tab/sheet called 'Transport Plan' in which the delivery coordinator needs only to enter two pieces of information at the top: date and vehicle type (from a drop down). An array formula will then return all rows from the main table that match this (i.e. all deliveries out on the 22/02/17 on Vehicle 2).
However the delivery order entered in the main table may not necessarily be in numerical order top to bottom (depending on how he wants to juggle the deliverers)... so this means that currently the data in the Transport Plan could look like this (these are the pulled in delivery orders as found in the main table)...
3
1
4
1
2
2
5
How can I re-order this in the transport plan so will be ordered as
1
1
2
2
3
4
5
??
The array formula I am currently using is:
=IFERROR(INDEX(PLANNING_SHEET_FULL_TABLE,SMALL(IF(COUNTIFS($M$2,PS_DELDATE,$M$3,PS_TPORT),ROW(PLANNING_SHEET_FULL_TABLE)-MIN(ROW(PLANNING_SHEET_FULL_TABLE))+1),ROW(A1)),MATCH(B$5,PS_PLANNING!$B$5:$AZ$5,0)),"")
I am thinking I could use a series of helper columns, first being the regular formula, then a column with same formula but showing row number, then a third column ranking the first column, then a 4th column with 1,2,3,4,5 ect going down the rows (depending on how many rows have data) then a 5th column doing an index match to order the row numbers by rank.
Finally I can then use an index match in the main transport plan table to use the row number as the row reference.
Is this the way to go or is there a neater / better way?
Thank you very much in advance!
Work sheet attached if required.
Kind regards.
Bookmarks