Adding criteria to an array formula

[jonagpa]

Hello guys!

I am seeking help here after being stuck on this for many many hours. I appreciate any help I can get. With that said because I am a beginner, I would prefer not to get answers that rely on VBA. VBA is still to complex for me to use, I prefer to learn and adapt standard formulas (the more I know how to do, the less i come and ask silly questions)

Anyways...I need to create an orders shipment report. Shipments go out once a week, as each week passes, one new report is created for that week. Because there are space restrictions, not all orders are shipped onetime, some orders must stay and be shipped the following week.

Ive added the below information to each respective sheet so that you do not have to switch back and forth between the forum and excel. Also if you wish, you can just go straight to the workbook and read there.

In sheets "Week 1" and "Week 2" we have the Orders table (left) and Departure Log table (right).
Notice week 1's departure date is 6-Dec and week 2's is 13-Dec. In the logs, notice that not all orders were shipped on their intended date, this is key for the criteria I am seeking to implement.

Sheet "Goal Report Week 2" is what the report for week 2 should look like (it was created manually to show the end goal, no formulas here)
Notice that all orders from week 2 are on the report, even the ones not yet shipped (status of "pending"). Also any order from week 1 that was shipped on 13-Dec (week 2's shipment date) is on the report (meaning that it does not contain all orders from week 1).

Sheet "Report Week 2" contains my working progress formula. I am only showing the New York section because the other locations will have the same formula respectively. To further simplify this and help us focus on one formula, we will ignore the status column. This is how far I've taken it. As you can see, I was able to fetch all the data from the current shipment (week 2) correctly (no help needed in this section of the formula). However, I am failing at fetching week 1's orders that were shipped on 13-Dec only. Because I do not know how to add the criteria where it checks if the order shipped on week 2's shipments date, I am getting all of the orders from week 1.

Thanks guys

[JohnTopley]

My advice would be to put all orders and departures onto one sheet with a column for week number and/or date.

You will at some point be faced with Week 1 orders that are shipped in Week 3 (!) and producing lists from multiple sheets is difficult without VBA.

You can produce your weekly reports as required but it will be much simpler.

See attached.

[jonagpa]

My advice would be to put all orders and departures onto one sheet with a column for week number.

You will at some point be faced with Week 1 orders that are shipped in Week 3 (!) and producing lists from multiple sheets is difficult without VBA.

You can produce your weekly reports as required but it will much simpler.

Unfortunately I can not use this approach because the people who is intended for will find it difficult to use. I can see them editing the wrong order once the number grows in the thousands. I have to make this usable to even the most basic excel users (PITA I know...).

I also oversimplified the workbook, I just didn't want anyone having to read too much or focus on the wrong thing. In order to address the week 1 shipped on week 3 issue was to have another table on each week (besides Orders and Log table), lets call it "Delayed" table. This table was going to look one week back and list the orders that were not shipped on time. On the subsequent week, it would do the same but this time it will also look at the previous week's "delayed" and get those orders too. Thus each week building on itself and always dragging/pulling all the delayed orders but each table only has to look one week back.

Hopefully I was able to convey my ideas correctly. back to the original questions, it what I am trying to do posible?

*I missed looking at the attachment before my reply, thanks for the reply and if I am unable to make it work I will use your suggestion.

[JohnTopley]

It is (almost) impossible to use the INDEX/SMALL combination over multiple worksheets (or the equivalent with multiple instances of a table on one sheet) where there is a variable (unknown) number of entries in a given table. (I have just written a macro for someone to overcome this particular problem!)

Your proposed solution of "Delayed" tables does not resolve the problem - it is just another instance of it!

Hence my advice: keep it simple. You may be able to get round "usability" problems in other ways. One of the "problems" with posting to the forum is the tendency to oversimplify the workbook so respondents can be directed on a path which is then negated by other unknowns (Donald Rumsfeld's "Unknown unknowns"!)


The only real alternative if you use multiple sheets is to use VBA with a button to invoke the macro(s).

[jonagpa]

thanks for the replies. You mentioned that it would be almost impossible to use INDEX/SMALL. Is there any other tools besides VBA that I could use? other formulas that I am not aware of?

BTW im not trying to be stubborn, I just want to explore this avenue more. trial and error is how most of us learn. thanks!

[JohnTopley]

No problem: I gained (and am still acquiring) knowledge by trial and error (plus reading replies on this forum from the experts).

The difficulty is having "dynamic" ranges on several sheets (maybe unknown number) which need to be consolidated in another sheet. I am not aware of any formulae which handle this: and it would be very complex if it exists.

What are the potential "usability" issues you think you (the users) might face which possibly preclude the single table option?

[JohnTopley]

For your consideration: a VBA solution.

The code is not complete but gives you an idea of what can be done.

The macro is initiated by the button on the "Dashboard" with Week selected from a drop-down.

The data in columns to the left of the shaded area are named ranges used in the macro.

The macro

(1) Checks that all data sheets "Week n" are present for "Report Week n": aborts if a sheet is missing

(2) Creates the "Report Week n" sheet for week "n" if it does not already exist

Please Login or Register  to view this content.

[jonagpa]

I appreciate the VBA code John, I think you were right from the beginning. ill go with your original idea instead. Ill have to put all weeks in a single sheet. I would still keep the log separate because the people entering that information should not see the order info. So what I plan to do is have the an "Orders" Sheet, and a "Log" sheet. Use Vlookup to get the date an order got shipped out from the Log sheet.

This brings up a potential issue. In order to keep my original question simple, I did not share that each order may be shipped in more than one part. You can probably already imagine what I am trying to figure out now that my original plan has change.

one easy way but very messy in my opinion is to have a column for each potential part in the Orders sheet. So say I expect an entire order to not have more than 5 part, I would have columns part 1 to part 5. then in the Report sheet, add an "or" criteria that look into each column for a match.

I would prefer not to use that method, so I thought of how could I simplify it into only one column. I think I should concatenate all the "date out" fields of the departure log table. so if a order was shipped out in different parts (exaple: "6-dec, 13-dec, 20-dec") they would be all on one cell. Now I can use the ISNUMBER([SEARCH(referance to a date out cell],[report date])) to create the criteria.

Which way should I go with? or how would you tackle the problem.

[JohnTopley]

Thank you for the feedback. I understand your reluctance to go with VBA and am glad you are going down the single sheets route for the Orders and Log.

My first question is: how are part orders presented to the customer: is the Order Number suffixed in some way e.g. 01234-1, 01234-2 (01234/1).

if this approach is/can be used then it is easy to identify the "master" order (01234) and its subsidiaries; so no extra columns are required. Equally all partial orders could be entered simply with the same Order number but different Log dates: how important is it to identify the partial orders ?

(Is all the data (Orders/Logs) entered manually or imported from another [e.g. Order Entry] system?)

I would certainly advise against using concatenated fields generally and date fields in particular as they will to be TEXT dates rather proper date fields. for dates you should always use proper date formats: simply change how they are displayed.

If an order is part-shipped on 06/12/2015 , 20/12/2015 and 03/01/2016 (weeks 1, 3 and 5) then I assume they would appear on the "Report" for weeks 2, 3, 4 an5 (?)

This "system" illustrates the potential problems of using a basic Excel application for what can be a fairly complex problem where there needs to checks on the integrity of the data. For example, checking for duplicate (erroneous) order numbers.

Hope this helps.

Please feel to continue this dialogue. If the thread starts to get too long you can PM me (perhaps with a Word document describing the issues)

[jonagpa]

HI,
Sorry I was absent and didn't reply. I was holidays got busy with the family (in a good way) but I am back to focus on this.

Q1: Right now we are not numbering orders with a suffixed. The customer gets a hand written receipt, the invoice was preprinted with the order number and the information is filled out on the spot (aka old school). This was not my idea, and I think many things need to change to improve the system. So there is only one order number, each part of an order is "tagged" with the order number, no suffix. I already had though of using suffix but right now I am tackling the mail problem and building up to that point. hope this made sense.

q2: All the data is imported manually I am working on making excel the main system and so all the data will continue to be entered manually.

Q3: if I should not use concatenated fields for the dates, I need to find the best approach on how to identify which orders should be in the reports for that week.

Q4: related to q3, So if an order was placed to be shipped Week 1, and it was partly shipped on week 1, 3 and 5, then it should appear on those week's report. Now IF it wasnt shipped on week 1,3 and 4 but instead in week 2,3 and 4, it will need to be in week 1 report also because thats when it originated even if it wasn't shipped that week. There is a long story for this need but short is that the person delivering will need to know if the package didn't come in. There will be a field that tells you how many parts of total parts arrived ( 0 or 5 parts). I already have this field working.

So now I have to decide how to approach the reports. I know the fields need to be in the same sheet and I could either use dates or week numbers. Since you mention that it is much better to use proper dates instead of concatenated, what if we concatenate the week number instead. Then search that one column and have it return any orders numbers that match the criteria.

If that is not the right way to approach it, what would you suggest I do?

[bebo021999]

Hi jonagpa,

It appears you are advance excel user and this thread must be an interesting one to deal with!

My approach:

1) WS Order_Shipment: All Order ID's must be registered here with Planed and Actual date to be keyed in and followed up.
Column O: Current status up to today
Column P : Status up to said week (helper column)
Orders are teared into possible parts (1 to 5 parts and can be inserted more columns)

2) WS Actual: Report for specific week number typed in cell B1. Leave B1 blank for all dates report (from 1-Jan-1015 to to day)

Any question, feel free to post here.

[JohnTopley]

I'll leave you to review the proposal from Bebo021999.

[jonagpa]

I WISH I was an advance user! maybe intermediate? anyways thank you bebo for responding.

your workbook could really work but unfortunately I do not have it set up this way. The reason being that it would take long and would be inaccurate to manually find and key-in the "actual" date. if you want I could share why with the forum, but I think people would get bored and leave

So instead, there is another table. This is the log table and orders get keyed-in as they are registered going out. So in real life the outbound orders are already separated and are sitting pilled up together in the "warehouse". A person goes grabs an order, puts it in the outbound vehicle and logs it, takes the next order and does the same, until completing the job. The person ends up with a list of everything registered out and does data entry to excel and copies the list as is. Order ID and Date out, that is it.


So I have attached a better representation of the 2 tables I have set up. Now your method could work, if part 1-5 "Actual" date was automatically grabbed from the second table "log table". Now I know how to use Vlookup, but that only gives you one result. "Large" function could help, so that on the "part 1" you could have the earliest date and by "part 5" the 5th largest date. Do yo think it would work? I cant actually write the exaple down because I do not know how to add a criteria for each order ID.

So I guess the real question is, is there a function that is similar to vlookup but instead will find or return all the values that match the criteria?Copy of excel-forum-jonagpa.xlsx

[bebo021999]

your workbook could really work but unfortunately I do not have it set up this way. The reason being that it would take long and would be inaccurate to manually find and key-in the "actual" date.

To follow one shipment, you need 2 steps:

Case of Order ID 1001, order date 6-Dec
1- Planning: On 6-Dec, you planned to ship ALL on 13-Dec: type 13-Dec on planned Part1. On that day, it shows pending
2- Follow up: On 13-Dec, it is actually shipped, type 13-Dec on Actual Part1

Case of Order ID 1003, order date 6-Dec
1- Planing: On 6-Dec, you planned to ship on 6 and 13-Dec: type 6-Dec on planned Part1 and type 13-Dec on planned Part2. On that day, it shows pending
2- Follow up: On 6-Dec, Part1 is actually shipped, type 6-Dec on Actual Part1. On 13-Dec, Part2 is actually shipped, type 13-Dec on Actual Part2

Similar to case ID1005. But, if on today, actual of Part3 and Part4 (planned to ship on 20 and 27-Dec) still blank, it means Part3 and Part4 is pending, until actual cells are filling.

During the time, planed cells can be adjusted to adapt the situation.

Hope it helps.

[JohnTopley]

See the attached where the shipment dates have been added based on data in Columns M-N

Formula is

=IFERROR(INDEX($N$4:$N$27,SMALL(IF($M$4:$M$27=$C4,ROW($A$4:$A$27)-ROW($A$4)+1,""),COLUMNS($A:A))),"")

Entered with Ctrl+Shift+Enter

[jonagpa]

Thanks JohnTopley and Bebo,

thanks to you guys I was able to get to this point. I took all your suggestions and used them to fit my needs. I have everything working the way I want to, the reports have the correct information. I was able to concatenate the array formula into one single column and most importantly I was able to use isnumber and search function to look into the text string to fulfill the criteria while querying reports.

I just have one more thing to ask you guys, the performance of the workbook has gone down considerably! at some points in takes 30 seconds to edit any of the array reference cells. This will be a no go for productivity and a pain in the neck to work with. I am considering ditching the entire array function for something less intensive.

I have attached a workbook with a working example.
quick explanation, It has 3 sheets: Orders, Log, Report. Orders could be considered the main table, order IDs are unique here and any pertinent information of the order is kept. Column D of this table contains the arrays, they simply look for the respective Order ID in the Log sheet (Column A) and return the shipped week (concatenates all results). the log table's only purpose is to register the departure of each order's parts and what Week it was shipped in. Column D is used in the reports sheet, I use the isnumber and search function to look in the text string, if I find a match it returns that Order ID.

The problem:
The array function queries the Log table and looks for all the shipped weeks for each order ID, it concatenates it all into one cell for later use. I need to either make the array formula more efficient or ditch it all together. I know you guys probably have run into the same problem and really need your advice on how to proceed. I expect to have about 15000 records in one year. If you multiply this by the array formulas I use, it would be aprox 75k arrays being performed (this is because I expect a max of 5 parts, one array per part per order).

Now Ive been thinking of a solution to get the same result without the array in Ordres!D. There has to be something that can return the cell location of an order ID (column A) in the Log Table. In my case there are multiple parts (maximum of 5 duplicate order IDs in the log table) and I need it to return the Week number (column B) for each instance. Once the location of each instance is known, I could reference the the adjacent cell which is the week numbers (column B) and concatenate all the results into one cell. Thus avoiding the entire array formula.Please let me know if something like this is possible in excel.

****if I lost you in the last paragraph, its ok, just look at column D of Orders sheet and see if there is any other way to get the same results without using an array.****

Please let me know if there is any other method to get the same information in Column D, or if there is a way to increase the performance of the array formula. Thank you again guys, I feel like I am almost there.

[JohnTopley]

See the "Orders" sheet in the attached where I have used helper columns to replace need for array formulae.

=IF(COLUMNS($A:A)<=COUNTIF(Log!$B$2:$B$1000,$B2),INDEX(Log!$C$2:$C$1000,MATCH($B2,Log!$B$2:$B$1000,0)+COLUMNS($A:A)-1,0),"")

requires "Log" data to be sorted by Order/Week

The COUNTIF could be replaced by using the "Parts" value in "Orders" table

Or

=IFERROR(VLOOKUP($B2&COLUMNS($A:A),Log!$A$2:$C$1000,3,0),"")

Requires helper column in "Orders" but no sorting (untested!)

or as alternative to VLOOKUP (helper column need not be in column A)

=IFERROR(INDEX(Log!$C$2:$C$100,MATCH(Orders!$B2&COLUMNS($A:A),Log!$A$2:$A$100,0)),"")

[jonagpa]

Hello guys, just wanted to come here and thank you guys for all the effort youve put into helping me out.

after viewing orderswb-V1 I realize that having a unique identifier was going to be key into getting this to work with out without having an array. So based on the workbook v1, I saw how the week number was added to the order ID, thus making 10011 for ID 1001 + the week shipped w1=10011. Now I could never really used that number and we will eventually move into using suffix to better track each part, but I came up with using count to count how many times each order is in the log, thus making the suffix. So if an order appeared 2 times, giving two unique numbers 1001-1 and 1001-2. It is all in the attached WB just in case someone has the same issue as me. Thanks again guys!

[JohnTopley]

but I came up with using count to count how many times each order is in the log

which was my solution in the "helper" in Orders in my last post (without the "-" delimiter). And if you recall, I suggested this as a solution very early in this thread

[jonagpa]

hey JohnTopley,

I didn't meant to imply that I came up with the idea, I know you had asked about the suffix from early in the thread and basically said it would be easier if done this way. However I had the idea that i did not yet need a suffix system to replace my normal order ID, this would turn one order into potentially 5 rows in my Orders Sheet. in hindsight I realize it did not need to replace my system, the suffix just needed to be paced in the log. However for someone new to excel, these suggestions are not apparent like to an advance user, so I wrote off your idea. I realize how to do it after seeing bebos work on WB-v1 a possible way to add a suffix. I am not by any means trying to take any credit and I apologize if I didn't take your advice, it stemmed from ignorance rather than hardheadedness .

[JohnTopley]

O.K - I'm glad that between us ( acknowledging the ideas from Bebo) that a satisfactory solution has been achieved. in particular you should now be able to bypass those "nasty" array formulas.

Come back if you hit any more "roadblocks".

[jonagpa]

Thank you John, I really appreciate the help!