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