Forum Discussion
Create a calendar from dates start and end
I came in to write out an explanation of how that formula works and realized there is a mistake in it, such that it worked well for that first example, but probably would not continue to work for all of the different ref items. A revised formula in a new spreadsheet is attached.
So here's a revised formula.
=IFERROR(LET(datearray,FILTER(BASE!$M2:$M6722,BASE!$A2:$A6722=$D5),status,MATCH(E$1,datearray,1),Wharray,FILTER(BASE!$L2:$L6722,BASE!$A2:$A6722=$D5),INDEX(Wharray,status)),"---")
By the way, doing this every day for so many years and so many items is a major consumer of computer power. You might want to reconsider your design, or only do a few rows at a time and then save the results as values rather than as formulas.
So let me break it down, from the inside out, explaining what the LET function does along the way.
FILTER(BASE!$M2:$M6722,BASE!$A2:$A6722=$D5)
This creates an array of the dates from your Base tab where the Ref equals what is in column D of "Calendar" -- so an array of the dates for Ref 18535, for example
LET then assigns that array to the temporary name "datearray"
FILTER(BASE!$L2:$L6722,BASE!$A2:$A6722=$D5)
Similarly, this creates an array of the data in column L (which you'll see I changed) where Ref equals column D of Calendar tab
LET then assigns that array to the temporary name "WHarray" (for Warehouse array)
MATCH(E$1,datearray,1)
Finds the first row in dataarray that is less than the date on the top row of the Calendar tab; LET assigns that value to "status"
INDEX(Wharray,status)
Uses status to find the word in WHarray that's in the same row -- which is why I changed those to be "Available" "Worksite" etc
the IFERROR function serves to take dates where there is no record and returns a blank (to make the spreadsheet easier to see; I initially had it return "No Rec" but that text doesn't really function helpfully.
OK, now we can see it. But some help in understanding what we're seeing would come in handy.
So far as I can see, the data in columns B, C and D is the same in every one of the rows. The others do change, but what are they--what do they represent and how do they pertain to the task you are wanting to accomplish?
The column A gives the reference number for one material
The column K says if the movement is an exit or a return (from a worksite) or a mutation (between 2 worksite), the first letter of the number gives this information : S for exit, M for mutation and R for return
I just need to create the calendar (schedule) of using for each material, day a day
- in a first step I will use only the data without "abnormality", in this way I should be able to identify big problems but after, to be accurate I need to rebuilt correctly the data
You wrote: I really need to now wich materials is used (to buy and avoid to be without this material with worksites asking for) and which is not used (to resell it and to let space in the warehouse)
And for that purpose, to know which materials are used, can't you simply ignore the fact that some of it was stolen, lost, or otherwise disappeared? Just use the data regarding its having gone out and back in (onsite, vs available)?
- You understood and I'm agree with you about this problem. I really need to now wich materials is used (to buy and avoid to be without this material with worksites asking for) and which is not used (to resell it and to let space in the warehouse)
Unfortunately, I think to develop a macro for solve that. This file is for just one materials, and we have more than 60 materials to manage, so it's impossible to change the column manually.
Thank you very much for your help ! I see. Well, one could probably write a macro to do it, but I'm not a user of macros.
That logistics center is "committing bad data" in the way they do it, because by putting different transactions, complete with different dates, they're muddling up the history. For example:
As I see it, then, you can either do it manually (or find somebody who could write a pretty elaborate macro) OR
deal with the question: "does it really matter?" Or maybe it's "To what extent does it matter?"
By which I mean, for the analysis that you're doing, should you just
(a) ignore the lots of material that have these conflicting rows? (i.e., erase them from the "Base")
(b) overwrite the "worksite"/"available" status indicators with "stolen"/"lost"/etc?
(c) work to move those conflicting columns to the bottom to get absolute and accurate and comprehensive data?
Only you can answer that set of questions.
- 🙂
If I had choice... This file is an extraction from the software which manages a logistics material center... Here: I've started doing what I talked about. You will have to finish it up, if you agree that this serves your purposes.
Notice too that I changed the formula that is in column L of your "Base" tab. It now uses the IFS function, instead of nesting multiple levels of IF. More efficient. Here's what it looks like. I've added the letters X, V and W (to take account of those last three status words.
=LET(Ltr,LEFT(K2,1),
IFS(
Ltr="R","Available",
Ltr="S","Worksite",
Ltr="M","MUTATION",
Ltr="X","Destroyed",
Ltr="V","Loss",
Ltr="W","Theft"
)
)
I've also moved some--not all-- of the data elements from columns N-Q to the bottom of the database (and extended references in the main formula in "calendar" to cover that.
The solution is to leave the formula as it is, and change ever so slightly the way you manage the database. And I'll explain that in responding to your two requests.
Firstly, there is a date for the day where the material is received (after buying) in our warehouse, so since this date and until the first exit, this material is available. For example, the material reference 18535 was received on the 29/11/2016 and this material was sent to a worksite on the 15/2/17, so from the 29/11/16 to the 14/2/17 including, this material has to appear "available". And before the 29/11/16, the material has to appear "..." (because it doesn't exist).
But there's no record for that receipt of material on 29/11/2016. That's why it doesn't show up as "available." All you need to do is add a record Rnnnnnn with that date, and it will appear.
Second, sometimes a material can be destroy or theft or loss. In this case the information is in the column O or Q (Base). So from this date the material has to appear "..." . For example, the material reference 18536 was destroyed on the 17/11/2017, from this date the material has to appear "...".
Same as above. Just add the date and a record Xnnnnnn to column K, and the word "Destroyed" to column L.
You can eliminate columns N-Q and handle them among the more "normal" transactions. In every case, you're changing the status, with a date associated.
If you need help doing that--if it doesn't make sense--let me know and I'll be happy to do it, but I think that should make sense. By the way, in case you haven't noticed, I did sort--and you should to--the entire database using this dialog box (the Sort command is on the Data toolbar) Especially if you add in the kind of transactions I talk about above; you'll want them to be sorted in order by those fields.
Good evening !
I may be pushing the envelope a bit... but I need to add 2 checks to improve the formula.
Firstly, there is a date for the day where the material is received (after buying) in our warehouse, so since this date and until the first exit, this material is available. For example, the material reference 18535 was received on the 29/11/2016 and this material was sent to a worksite on the 15/2/17, so from the 29/11/16 to the 14/2/17 including, this material has to appear "available". And before the 29/11/16, the material has to appear "..." (because it doesn't exist).
Second, sometimes a material can be destroy or theft or loss. In this case the information is in the column O or Q (Base). So from this date the material has to appear "..." . For example, the material reference 18536 was destroyed on the 17/11/2017, from this date the material has to appear "...".
I hope not to be too asking. These kind of formula is difficult for me.
No problem if you can't help me. You made already a lot.
Thank you
David
- You are welcome. I learned a few things myself in the process of answering you, which is always fun.
I came in to write out an explanation of how that formula works and realized there is a mistake in it, such that it worked well for that first example, but probably would not continue to work for all of the different ref items. A revised formula in a new spreadsheet is attached.
So here's a revised formula.
=IFERROR(LET(datearray,FILTER(BASE!$M2:$M6722,BASE!$A2:$A6722=$D5),status,MATCH(E$1,datearray,1),Wharray,FILTER(BASE!$L2:$L6722,BASE!$A2:$A6722=$D5),INDEX(Wharray,status)),"---")
By the way, doing this every day for so many years and so many items is a major consumer of computer power. You might want to reconsider your design, or only do a few rows at a time and then save the results as values rather than as formulas.
So let me break it down, from the inside out, explaining what the LET function does along the way.
FILTER(BASE!$M2:$M6722,BASE!$A2:$A6722=$D5)
This creates an array of the dates from your Base tab where the Ref equals what is in column D of "Calendar" -- so an array of the dates for Ref 18535, for example
LET then assigns that array to the temporary name "datearray"
FILTER(BASE!$L2:$L6722,BASE!$A2:$A6722=$D5)
Similarly, this creates an array of the data in column L (which you'll see I changed) where Ref equals column D of Calendar tab
LET then assigns that array to the temporary name "WHarray" (for Warehouse array)
MATCH(E$1,datearray,1)
Finds the first row in dataarray that is less than the date on the top row of the Calendar tab; LET assigns that value to "status"
INDEX(Wharray,status)
Uses status to find the word in WHarray that's in the same row -- which is why I changed those to be "Available" "Worksite" etc
the IFERROR function serves to take dates where there is no record and returns a blank (to make the spreadsheet easier to see; I initially had it return "No Rec" but that text doesn't really function helpfully.
Note: the formula and spreadsheet attached to this reply contain an error. I've fixed it in the next post. I've also crossed out some portions of this reply because of the need to correct. The material that hasn't been crossed out is still valid.
I hope you have the most recent version of Excel, because my solution involves both FILTER and LET functions.
Here's the formula that's in cell E5, and then copied across as far as column DDC (hence my total count of "Available" differs from yours.=IFERROR(LET(datearray,FILTER(BASE!$M2:$M6722,BASE!$A2:$A6722=$D5),status,MATCH(E1,datearray,1),INDEX(BASE!$L2:$L6722,status)),"No Rec")Because I made it read "No Rec"--meaning "no record"--if in fact the earliest date of any activity was later than the date on the top row, dates before 15/2/2017 show as "No Rec" rather than "available". (I think mine is therefore more accurate.) That could easily be fixed if in fact it is available, by entering an R transaction with your earliest date.You'll also see that I changed your column L so that it more directly answers the question "Available" or "Worksite" or "Mutation" -- it's redundant to have it just translate the S or R or M. Might as well put it to work.
I didn't try to deal with the Destruction. If you want more, come back and ask.
- I need to go back five years ago because there is some exceptional years during thi speriod (2 covid years) so for my analysis I prefer to check on this big period
- Sorry, you right and this is not easy for me to explain clairly. I try to give you more explanation now :
I'm managing a center (wharehouse) which the goal is to supply material for worksite (rent)
When a material is send from the center to a worksite, the soft we use save the information and it gives a number for the movement with the letter S, as "S000203". At this time the material is not available (it's using on a worksite)
When the worksite doesn't need a material, he sends it to the center, at the arrival at the center, there is a man who use the same soft and he records the material, the soft send a movement number with a R as "R01004449"
During the period, from the movement "Sxxx" and "Rxxx" the material is not available (it's using on worksite), but from the return until the next send, the material becomes available (it's in stock in our wharehouse)
When a first worksite has a material and another worksite need the same, sometimes the first worksite send the material directly to the second worksite, in this case my wharehouse receives te information and the soft records this information and gives a movement number with the letter M for mutation as "M01002280"
So in the column A, you can see all reference number for our material, and in the column M the date where a movement has been done. The kind of movement is indicated in the column B thank to the first letter I'm sorry; it's still not making a lot of sense to me. You have to realize that you are very familiar with not only the data, but what you are wanting to do. For those of us looking in from the outside, it's still needing some explanation. It would be easy if we were in a room together; communicating by message board always requires a bit more attention to detail.
I'm not asking you to write the formula (or the macro), but to explain verbally what would lead to saying "available" in one column, "worksite" in another. And, while you're at it, do you really need to go back six years? And forward into 2024?
What is the overall purpose or value of this kind of retrospective inventory?
- columns N O P and Q gives informations if the material doesn't exist anymore (because it was lost, theft or destroy), at this date the material becomes unavailable forever