Auto concatenate and reverse concatenate & Remove duplicates .

We have a workbook in which there are 2 sheets - "CP + Party" & "Auto update"
So everyday the used rows in sheet "CP + Party" changes, depending on used rows count in "CP + Party" we need to apply a straight Concatenate formula in "Auto update" sheet starting from cell A2, As =CONCATENATE('CP + Party'!B2,'CP + Party'!C2).
In the attached example workbook the used rows in "CP + Party" sheet are 301 rows(Including header), so in "Auto update" sheet we need apply straight Concatenate formula until row 301 and starting from row 302 we need to reverse the Concatenate formula as =CONCATENATE('CP + Party'!C2,'CP + Party'!B2) text 1 and text 2 are flipped & pull it until row 601 (601 row because the used rows are 301 in "CP + Party" so we are doubling the count to get complete reverse concatenate data ).

Once the above is done we can remove the duplicates in "Auto update" sheet Column A.

I have tried doing this myself with the help of google codes , but tricky part is finding a logic to reverse the concatenate formula range based on row count in "CP + Party" sheet, Help in need, please help.

Hello
Try the following macro, I believe it does what you require if I've understood correctly:

Please Login or Register  to view this content.

Same result, bit faster

Please Login or Register  to view this content.

Kind regards
Leo

Thank you both DBY and LeoTaxi, it worked with 100% perfection.