Place formulae on each row where dates meet criteria

[VISHAL120]

Hello Everyone,
I having some issues to do this planning project which am working on. I would be very grateful if I can get some guidance on how to do it either by formula or vba please.
I have attached a sample of the data for better understanding.
I am using actually this formula which takes data from several columns and concatenate for the visual view of the user rather than going through the big file to see the information.


The formula is as such :


=IF(CE$10=$BF11,$C11&"-REF: "&$G11&"-TDG: "&$AT11&"-LOADED LINE"&$AO11&" - ORDERED: "&$H11&" - FAB: "&TEXT($K11,"DD-MMM"))&"- CUT DATE: "&TEXT($AA11,"DD-MMM")&"-SEW: "&TEXT($BF11,"DD-MMM")&" TO "&TEXT($BG11,"DD-MMM")&" - DEL: "&TEXT($BP11,"DD-MMM")


It check is the date from the planner which is actually CE10 equals to the Start Date Sewing column which is actually BF11 and if the dates meets then it will starts concatenating the data as on the formula from several columns which shows a result as such below:


RESULTS: TIM-REF: DRESS-TDG: 22.59-LOADED LINE3597.79 - ORDERED: 3493 - FAB: 27-Feb- CUT DATE: 16-Mar-SEW: 04-Apr TO 24-Apr - DEL: 15-Apr


1. After that I will have to color the rows till it meets the End Date Sewing Column which here as example is 24/04. Can see the attached sheets for example.

2. Then I will have to merge the cells together till the end colors cells so that the column start from CE11 to CR11 shows only the data for this specific order.

For the other orders I will repeat the same whole process by copying and pasting on each start Sewing Date .


I have also another formula which am unable to place as its too many arguments:


=IF(K12<>"","T"&" "&I12<>"","A"&" "&R12<>"","O"&X12<>"","OF"&" "&AF12<>"","VA"&" "&AG11=<>"","L")


This one it shall check from example the Bulk fabric date Column ( K12) is not blank then assign the value “T” and if the achieved column which is L12 is blank then it shall turn the value “T” to red showing the fabric still not in . And keeps doing same for other parameter to check also same way.
Can someone please guide me on how I can proceed with it either by VBA or Even Formula please.

Actually we are doing this manually and its very very time consuming. We can complete this report like in 6 -8 hours only as there are many data like this to treat and every changes that happen we shall again update it manually again losing a lot of time.


Thanks in advance for any even a small guidance please.

[VISHAL120]

Here below a sample file of the expected results. thanks for helping in advance.

the sheet Raw data: is what we have to treat and manage.
the sheet Result : is the data after we have treated and manage to give the user the ease to see all in the same place.

[p45cal]

The attached has macro blah to get you started. Run it.
I don't know what you want appear in the 2nd of pairs of rows.
Please fill in what you want to see for at least 2 rows in you file and I'll try and put something together.

[VISHAL120]

HI P45Cal,

Thank you so much for your kind help and guidance. The code you place is working well and it starting on the day as stated on the Start Date Sewing column.
But the row shall be in the color as per the customer define color see the attached file (vbaExpress64941TESTFILE3.xlsm) below on the sheet result as i have place the customer define color on the top on range (A4 to B8) and i have also colored the required information as per the customer color manually for all the rows. can you see the possiblity of doing it as such please.

for the row you mentioned i have been able to place the formula so you can understand what information shall be there.
Its this formula which goes there : =IF($K12<>"","T"&"-"&IF($I12<>"","A"&"-"&IF($R12<>"","O"&"-"&IF($X12<>"","OF"&"-"&IF($AF12="VA","VA"&"-"&IF($AG12="L","L")))))) the output is like that : T-A-O-OF-VA-L

If you go on the formula you will see its checking for now only on the target dates column which are based on the row 2: COLUMN K2,I2,X2,R2,AF2 and AG2.

for each of these column for K2,I2, R2, AND X2 if the ach date column is blank that is for example :
FOR Col K2 if Col L2 is blank then it shall change the color of Letter "T "to Red Color ( this shows the user that the fabric date is not acheived yet and if is not blank then change to Green showing the user the Fabric date is acheived.

for COL I2 if column J2 is blank it shall change the letter "A" to Red color and if not blank then change to GREEN.

for COL R2 if column S2 is blank it shall change the letter "O" to REd Color and If not blank then change to GREEN.

for COL X2 if column Y2 is blank it shall change the letter "OF" to REd Color and If not blank then change to GREEN.

like as an example it shall be as this when seen :<< T-A-O-OF-VA-L >>which shows that the fabric ach date and the accessories Ach date is blank whereas the O and OF is green as the achieved date column is not blank.

Also if you the see the sheets Final Result, it will how the data will be display as a final result.

Thanks again for your time and kind guidance .

[p45cal]

In the attached I have included colouring the cells according to the customer colour.
I have not looked at changing the colouring of T-A-O-OF-VA-L. Maybe if I get the time and inclination I will do so later.

[VISHAL120]

Hi P45Cal,

Thank you so much for your kind help its working well. But Please when you have time please see the colouring of T-A-O-OF-VA-L as this one we will still have to go to change it one by one manually on almost 2000- 3000 rows which still will be time consuming. Thanks a lot again brother for your time and i really appreciate your quickness to respond. :clap:

[p45cal]

the colouring of T-A-O-OF-VA-L
In the attached the macro blah does few error checks and is quite 'raw' and not especially robust. You asked for guidance in your first message in this thread, however it begins to look more like you want it to be written for you; guidance is what you're getting.
Step through the macro blah in the attached using F8 on the keyboard, whilst doing so have the Locals pane showing in the VBE and observe the variables, and watch what happens on the sheet.
(By the way, I'd remove the hotch-potch of conditional formatting you have on the raw data sheet and consider removing the custom formatting of cells in the range below and to the right of cell C11)

[VISHAL120]

HI P45Cal,

Thank you very much for the guidance i wil go through it and analyze to make it adapt as per the requirement on the main file and will check also the speed its running to display the results.

However when am changing the formula on the <=IF($K18<>"","T"&"-"&IF($I18<>"","A"&"-"&IF($R18<>"","O"&"-"&IF($X18<>"","OF"&"-"&IF($AF18="VA","VA","N"&"-"&IF($AG18="L","L",""))))))>>

AS i have added part shown in RED IF($AF18="VA","VA","N"&"-"&IF($AG18="L","L","" its displaying the results as such T-A-O-OF-VA instead of showing the result completely like this: T-A-O-OF-VA-L
i have change the value of cell AF18 to VA and AG18 To L for testing. can you advise on this please. As am thinking maybe because of too many arguments that is why its not showing the complete result.

Also is there a possibility to display the results same as on the sheet FINAL RESULT please. This is because of the numbers of rows it has so the we can see the all the information on the same row for the concern Chaine Name.

As Column B to Column BP we will hide it after the calculation that you have shown is done. And we will delete all the rows which is for the same Chaine Name which has been move upward.

thanks a lot for your kind help and guidance and really appreciate your support.:thumb

[p45cal]

However when am changing the formula on the <=IF($K18<>"","T"&"-"&IF($I18<>"","A"&"-"&IF($R18<>"","O"&"-"&IF($X18<>"","OF"&"-"&IF($AF18="VA","VA","N"&"-"&IF($AG18="L","L",""))))))>
I'm not sure what you want to see in the result, however this might do it:
=IF($K12<>"","T-"&IF($I12<>"","A-"&IF($R12<>"","O-" & IF($X12<>"","OF-" & IF($AF12="VA","VA-","N-") & IF($AG12="L","L","N")))))
If you want to incorporate whatever your final formula is into the macro, you need to have a fully tested and working final version of the formula in cell CE12, then record a macro of you editing that formula in that cell and pressing Enter. You will see a line like:
ActiveCell.FormulaR1C1 = "=IF(RC11<>"""",""T-""&IF(RC9<>"""",""A-""&IF(RC18<>"""",""O-"" & IF(RC24<>"""",""OF-"" & IF(RC32=""VA"",""VA-"",""N-"") & IF(RC33=""L"",""L"",""N"")))))"
where you need to take the part in blue and put in the obvious place in the macro.

Also is there a possibility to display the results same as on the sheet FINAL RESULT please. This is because of the numbers of rows it has so the we can see the all the information on the same row for the concern Chaine Name.I have contributed enough. Perhaps someone else would like to step in. Maybe it's time for you to pay a programmer to do this?

[VISHAL120]

HI P45Cal,

I am just testing it on my main data as the sample file its working perfect but on the main file even the column L12 has date it keep showing red instead of Green. Can you advise on what range you remove the conditional formatting please, may be this is the problem.

[p45cal]

Can you advise on what range you remove the conditional formatting please, may be this is the problem.Remove CF from all the cells that might hold the likes of T A O OF V A L

[VISHAL120]

Hi P45Cal,

I know you have contributed a lot this is really helping me and my colleagues to make the work easier for us and for the team as well. we thank you from the bottom of our heart for this help and for your precious time. for Placing the data in one row we will see to do it manually by deleting the blank rows and moving the data up.

for the last post as you advise even i have deleted all the conditional formating its still the same. If you just for a last time have a look at the Main file. This one is the main file where we have trim some of the data just to make it lighter. i have already place all the code you advise since the start in it but its still seems to be keeping the same color even the column has the dates or not. please just a last time if you just have a look and advise or make the a correction on it please. we will really appreciate your help.

[p45cal]

In the original sample file, each pair of rows had identical information in both rows. In this file, often they are different, for example J13 is different from J14.
Do you want the T A O OF V A L cells to take data from the same row as that cell or the row above?
Assuming you want it from the same row then change these 4 lines:

If Left(.Value, 1) = "T" Then .Characters(Start:=1, Length:=1).Font.Color = IIf(.EntireRow.Cells(, "L").Value = "", -16776961, -11489280) ' T
If Mid(.Value, 3, 1) = "A" Then .Characters(Start:=3, Length:=1).Font.Color = IIf(.EntireRow.Cells(, "J").Value = "", -16776961, -11489280) ' A
If Mid(.Value, 5, 1) = "O" Then .Characters(Start:=5, Length:=1).Font.Color = IIf(.EntireRow.Cells(, "S").Value = "", -16776961, -11489280) 'O
If Mid(.Value, 7, 2) = "OF" Then .Characters(Start:=7, Length:=2).Font.Color = IIf(.EntireRow.Cells(, "Y").Value = "", -16776961, -11489280) 'OF

[VISHAL120]

Hi P45Cal,

sorry for the late reply i was in the hospital just came back.

Sure i will try it and thank you a lot for the help. its working fine now and we are saving a lot of time out of it and less error also.