I have a dataset that is like the tab 'Data' on the attached spreadsheet. I know that someone here can help because this forum is the very best for excel programming. The data shows sales order lines (one sales order can have many lines) and the date/time that the lines were moved between different status codes, as well as which program moved the line. The actual dataset is probably around 2000 lines.
I have thought of one idea to achieve the results I'm looking for but there most certainly could be a better way -- if someone else knows it, please reply! My method (if I were doing this by hand) would be to:
(1) Remove extraneous lines. The only lines of interest in this set are the very earliest times that the unique line is at status code 530 and the earliest it is at status 540. (NOTE: The line where it is moved to status 540 will always have Program ID: P55MANDT, but the line where it is moved to 530 can be done by various programs)
(2) In a new column, calculate the number of days (hours would work too) between the time the unique sales order line was moved to status 530 and the time it was moved to status 540.
(3) Count the number of unique sales order/line combinations that exist on the cleaned data sheet.
Like I said, if you can think of a better or more efficient way, then by all means please post that, sometimes it helps to have a fresh set of eyes looking at your data. I thank you in advance for your ideas and contributions!
Bookmarks