Need help with automating a workflow using a couple of spreadsheets

[Rahul_Rishi]

Hello Team,

I have 2 workbooks (WB1 and WB2) which have 1 sheet only(same Sheet name: "Test"), same 1st row Headers(A,B,C,D). Column A on WB2 has some values which are also present in WB1. I want to:

1. replace column B values on WB1,WB2 with values "ST" and "UV" respectively across the last row.
2. copy the entire sheet values on WB2 to append them to WB1.
3. Sort Column A in ascending order to see the same values together.
4. Remove Duplicates from Column A.

Can anyone help me with the code to automate this entire workflow? I am ok with Power Shell and VBA

Thanks & Regards

[#2]

Have you tried Data, Get Data, From File, From Folder?

[#3]

@Jan Karel Pieterse  thanks for your response but i did not understand what your solution meant? I need to automate the entire workflow. 2 or 3 clicks! Need the scripts in powershell or VBA.

[#4]

My point entirely, once you've set up the import using Get & transform, subsequent imports take no more than a click on Data, Refresh all. How the query must be defined depends on your exact situation.

[#5]

kindly upload sample data so we can get some more idea