Date manipulation on cert. criterias, no formulas on cert. cells

Hi guys.

I'm trying to sort out some macros to help myself and some older colleagues. I need possibly about 5-6 separate macros that I would run one by one depending what information I have available. The idea behind it is to populate as much as lines using the information available and rest I would have to go through line by line, check the systems etc. All macros are date related. There are multiple important columns that may contain important information where the code can get instructions how to behave. I have asked advice on earlier forum and I think all this can only be done via VBA codes (where I suck I'm afraid). Record new macro can only take you so far.
I have attached the sheet that I will be using daily basis. Two columns are censored but rest is all there. I have hidden some columns for the convenience to only see what is important but feel free to un-hide at any time. You may also change any dates whatsoever to test the codes.

There is a header row of-course. The columns that will be used at one point or another are:
G, H, M, S, V, W, X, Y, Z, AA, AB
The main column where all the info should be populated is S which is titled as Comments. Everything should be row specific. Because I will run things in waves and I will not always even have all info, there can not be any formulas in column S otherwise if you go for the next round it would wipe everything off. Quite few macros will depend of info in multiple columns and to make things even harder. the date that will also depend of day of the week it would try to land on. It usually takes us 3 days to deliver something, and if the date would near weekend we have to deliver it upcoming Monday or Tuesday.

I will list the main macros (once we have the basics right there will be only few alterations I would imagine). I will red color the ones that will be done at any given moment so if anyone joins this thread knows where we are.

1. Populating the word "Complete" to col S, using data from col Y, behavior does not depend of other columns
2. Populating a Date to col S, using data from Col V, behavior depends on col G, H, M and X, and also of the day of the week it would land on.
3. Populating a Date to col S, if condition is met in Col W, then it uses dates in Col G and H, the behavior depends also on col , M and X, and also of the day of the week it would land on.
4. Basically same as number 2 only difference is where it takes its data ie col Z
5. Basically same as number 2 only difference is where it takes its data ie col AA
6. Basically same as number 2 only difference is where it takes its data ie col AB

I will start with the first one which is probably the easiest:
1. If there is a letter C in column Y then, it should populate the word "Complete" to col S.
Remember you cant place any formula to column S.


I do hope some of you guys will have time to help me as it would make a massive difference in my life. That is if its even possible to such a complex stuff.

Cheers,
Rain

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

I'm curious to Airbus's reward.

As recommended by snb by PM I will list the full details of all macros how they need to behave and run. I will tell more about of why I need to get these macros in the following paragraph but if you just want to know what the macros should do you can skip it and go to my next post.

SNB how did you figure out that its to do with Airbus? But yeah they are our customers. We are between them and the suppliers. I'm one of the vendor controllers or MRP planners or how ever you would call it. Not very nice spot to be if you have backlogs and you don't get all the necessary info from your own suppliers. And I have to give answers to Airbus (column S) of this form. And I have to do it every day. At moment I do it this way. I use vlookup against some reports that I can run to get as much as info to column v, w, x, y. I wont always have info for Z, AA or AB. Then what I do is I start to filter those columns and I will get some info across to column S. Meanwhile I have to be very careful and notice info in certain columns. Its dead easy to make mistakes and it is very time consuming. After I have done that I normally still have some blanks on my comments section. With those I have to go line by line and figure out. Normally those blanks mean that something is wrong. I always find it hard to find any time to do fill them blanks. I would be happiest man on earth if I could use macros to get all that info automatically across to col S so I would have more time to work with the blank ones. So if anyone can contribute or help me I would be very thankful. I would share this with my other colleagues who have different suppliers. Also if anyone else from the industry comes across to this macro they might find it also very useful. I'm sure they would be thankful as well.

The "MAIN code"
IF col S is, IF col Y equals, IF col A starts with, IF Col W contains, IF col V is less than ()Today, IF col M is, IF col X contains, IF date difference between Col H and (Col V date + normal date rules) is greater than negative 5, IF not then Col V + normal date rules

IF col S is
Remember that Col S can never contain any formulas when running through with macro, its simply because it will have some manual inserted data in it. I do not want to wipe out any of the existing data. But having some formulas temporarily in the blank cells is OK.
IF col S is blank carry on with the "Main Code"
IF the Col S is not blank then follow the "Info in column" code

IF col Y equals
IF col Y does not equal letter "C" then carry on with the "Main Code"
IF col Y equals letter "C" then follow the "letter C" code

IF col A starts with
IF col A does not start with 1200 then carry on with the "Main Code"
IF col A starts with 1200 then follow the "Commercial order" code.

IF Col W contains
IF Col W does not contain letters "QM-Lot" then carry on with the "Main Code"
IF Col W contains letters "QM-Lot" then follow the "QM-Lot"

IF col V is less than ()Today
IF col V is equal or more than ()Today then carry on with the "Main Code"
IF col V is less than ()Today then follow the "Date in Past" code

IF col M is
IF col M is blank carry on with the "Main Code"
F the Col M is not blank then follow the "SSS" code

IF col X contains
IF col X does not contain letters "QN" carry on with the "Main Code"
IF col X does contain letters "QN" then follow the "QN" code

IF date difference between Col H and (Col V date + normal date rules) is greater than negative 5
IF date difference between Col H and (Col V date + normal date rules) is not greater than negative 5, then carry on with the "Main Code"
IF date difference between Col H and (Col V date + normal date rules) is greater than negative 5, then follow the "FT" code

IF not then Col V + normal date rules
IF not then Col V + normal date rules



The "Info in column" code. IF the Col S is not blank, do not over write it. Leave it untouched. I have left row 12 for the testing purpose.

The "letter C code
IF Col V has no valid date in it, IF col M is blank, IF col Y equals letter "C" it will the populate the word "Complete/Shipped". I have left row 13 for the testing purpose. It should read "Complete/Shipped"
IF Col V has valid date in it, IF col M is blank, IF col Y equals letter "C" it will the populate the word "Conflict with C" . I have left row 14 for the testing purpose.
IF Col V has no valid date in it, IF col M is NOT blank, IF col Y equals letter "C" it will then populate the word "SSS/complete". I have left row 15 for the testing purpose. It should read "SSS/complete"
IF Col V has no valid date in it, IF col M is not blank, IF col Y does not equal letter "C" it will then follow the "SSS" code

The "Commercial order" code.
IF col A starts with numbers 1200 then populate the word "Commercial order".
I have left row 16 for the testing purpose. It should read "Commercial order"

The "QM-Lot" code.
IF Col W contains letters "QM-Lot", IF col M is blank, IF col X does not contain letters "QN" then populate words "QM-Lot". I have left row 17 for the testing purpose. It should read "QM-Lot"
IF Col W contains letters "QM-Lot", IF col M is not blank, IF col X does not contain letters "QN" then populate words "SSS & QM-Lot" I have left row 18 for the testing purpose. It should read "SSS & QM-Lot"
IF Col W contains letters "QM-Lot", IF col M is not blank, IF col X contain letters "QN" then populate words "SSS & QM-Lot & QN" I have left row 19 for the testing purpose. It should read "SSS & QM-Lot & QN"

The "Date in Past" code
IF col V is less than ()Today (today not inclusive), IF col M is blank, IF col X does not contain the letters "QN" then populate the words "Date in Past" I have left row 20 for the testing purpose. It should read "Date in Past"
IF col V is less than ()Today (today not inclusive), IF col M is not blank, IF col X does not contain the letters "QN" then populate the words "SSS & Date in Past" I have left row 21 for the testing purpose. It should read "SSS & Date in Past"
IF col V is less than ()Today (today not inclusive), IF col M is not blank, IF col X contains the letters "QN" then populate the words "SSS & QN & Date in Past" I have left row 22 for the testing purpose. It should read "SSS & QN & Date in Past"
IF col V is less than ()Today (today not inclusive), IF col M is blank, IF col X contains the letters "QN" then populate the words "QN & Date in Past" have left row 23 for the testing purpose. It should read "QN & Date in Past"

The "SSS" code
IF Col V has no valid date in it, IF col M is not blank, IF col X does not contain the letters "QN" it will the populate the word "SSS". I have left row 24 for the testing purpose.
IF Col V has a valid date in it, IF col M is not blank, IF col X does not contain the letters "QN" it then populates the word "SSS" add space and then calculate the date according to the DATE RULES. I have left row 25 for the testing purpose. It should look like "SSS 07/02/11".
IF Col V has valid date in it, IF col M is not blank, IF col X does contain the letters "QN" it then populates the word "SSS & QN" I have left row 26 for the testing purpose. Double check if its correct

The "QN" code
IF col M is blank, IF col X contains the letters "QN" (not case sensitive please, as it could be anything as its text manually inserted by different people, but it will always have letters qn at least somewhere) it should then populate letters "QN". I have left row 27 for the testing purpose.
IF col M is not blank, IF col X contains the letters "QN" it should then populate the words "SSS & QN". I have left row 28 for the testing purpose.

The "FT" code
IF date difference between Col H and (Col V date + normal date rules) is greater than negative 5 then it should populate the word "FT" add space and then the date according to normal date rule. I have left row 29 for this testing purpose. It should produce "FT 08/02/11"
Now let me explain this one as its bit tricky. First of all what do I mean by negative difference. By that I mean that our current promise date (col H) to deliver the goods to our customer are 5 days earlier than our realistic delivery date. Our realistic delivery date is the date when it will be delivered to us (Col V) plus few days to get it to our customer (+normal date rules). Lets have an example. Or promise date to deliver to our customer is 02/02/2011 (Col H), but we ourselves will receive it on 04/02/2011 (col V). Now if you add the normal date rules to it which would be 4 days in this case, it would be 08/02/2011. So in that case we would be 6 days late. So we need to Fast Track it to not be late, or if we already are very late, we still need to deliver it ASAP as customer has waited it for a long period. So i would like to have that FT before the date.
But it cant be just a date difference. It needs to be negative date difference. Lets say it will be like this. Our customer wants it 08/02/2011 (col H), we will get it on 10/01/2011 (note its January not Feb), then we add our delivery times (normal date rules), which in this case would be 3 days so we would get it 13/01/2011. Which is something like 26 days before we need to ship it. So we are way ahead on schedule, so I do not want to have that "FT marking before the date. I have left row 30 to test that it wont include FT.

Later I would want to have 3 more macros but everything would be exactly the only difference would be that Col V, would be replaced by Z, AA, and AB


Again if anyone could help me I would be most sincerely grateful.
Kind regards,
Rain


Forgot to add new updated xls for the testing purpose. Well here it is

I think I lost the 'grand design'.

Recapitulating:

column S can contain the following values:
- Complete Shipped
- Conflict with C
- SSS / Complete
- Commercial Order
- QM-Lot
- SSS & QN-Lot
- SSS & QM-Lot & HQ
- Date in Past
- SSS & Date in Past
- SSS & QN & Date in Past
- QN & Date in Past
- SSS
- SSS 07/02/11
- SSS & QN
- QN
- SSS & QN
- FT 08/02/11

I s this correct ? And if so,
Can you reduce the number of possible values, or do they each reflect different actions ?

Originally Posted by snb

I think I lost the 'grand design'.

Trust me quite few times I was also lost. Maybe I should kept it really simple somehow.

You missed just a regular date like 01/01/2011. And SSS & QN is twice on the list but it doesn't matter as it can be reduced if necessary.

List can be reduced down. The ones remained would reflect in different action. They would be in this priority order. I.e. if SSS conditions and QN conditions would be met, it should show then SSS.
- Commercial Order
- Complete Shipped
- SSS
- QN
- QM-Lot
- Date in Past
- FT 08/02/11
- 01/01/2011


Do you think you could pull it off? Can it be done that any manually written info in col S wont be erased?

Cheers
Rain

second step:

now the possible results are clear you can make a table.

put the possible results in rows.
add a column for each relevant sheetcolumn (or combination of sheetcolumns).
Indicate in the intersecting cell the value of that sheetcolum to get the rowresult.

eg.

___________________Y _______V_____M____A___W___X__H/V-diff
Commercial Order___"C"_____Date
Complete Shipped___"C"_____Blank
SSS________________#NA__
QN_________________#NA__
QM-Lot_____________Blank__
Date in Past_________Blank__
FT 08/02/11_________
01/01/2011

Can you please compose an Excel sheet in which records with all possible outcomes reside + the indication which value should emerge in column S after running the macro ?

I have now completed the xls. I hope I understood it the correctly what was asked to be done. Made one small adjustment with FT rule. I hope its ok.


Two more days and its weekend. I'm bit stressed out, haven't still managed to find a flat in Bristol.
Cheers

cfr. the attachment to show the results I got using your first posted file.