Code to extend formula on changing ranges

Hi Experts,

I am pretty new to VBA, I am trying to automate a task where I have to remove duplicates from my table, sort the table, insert a formula and change formula to consider all the cells within the table. So far I was able to complete 2/4 steps and was able to create a formula I am trying my best however I am stuck at one point where I have to "extend" formula in one cell to take account of all the newly added rows because next time when I paste the new table in my macro it may have more/less rows and based upon that I have to change my formula which is kept in one cell.

I have also attached my working macro. I have added a separate tab called "Example" where I have outlined my steps. I am thinking the future process to be

1. Paste the data table in "Input" Sheet of macro
2. Run the macro and have result ready in cell F5 (Could be any cell) of "Input" sheet
3. I also need to copy and paste this result in a single cell of master table's corresponding row however I do not bother anyone about this step - I will manage it

If you paste the table (A3:C14) from "Example" sheet to A1 of "Input" sheet and run the macro1 you will see that I have removed duplicates+sorted the table based on column A. Can somebody suggest a code that would have updated formula in F5?

I have very basic knowledge of programming

Thanks for your time.
Best,
Bunty

Thank you soo much Dave, it worked like a charm I really really appreciate your help!!!!!

I have changed the code as per your suggestion and I have the output in "Output" sheet cell A2. Now I am left with conditional format, I need to color just "AND"/"OR" in this cell, Is it possible? I dont see any option to attach the revised macro hence writing the final output statement below.


Monday OR Tuesday AND Thursday OR Wednesday OR Friday AND Saturday OR Sunday


Best,
Bunty

Thank you so much Crooza, it is working fine except a situation where there is OR in the sentence (Which should't be highlighted) I understand it's tricky but can we instruct our program to exact match "OR" and "AND" and only color those?

I am facing below problem, you will see that it is also coloring the OR of COLOR/AND of CANDY

COLOR AND CANDY OR FUN

I appreciate your time to look in to this.
Best
Bunty

I apologize for that, I should have used a better example.

Just wanted to let you know this time it worked perfectly, I really appreciate your time. Cant thank you much. You are a life savior. Thank you thank you thank you

Have a great weekend!

Best,
Bunty