Copying unique values (between columns A and B) from column A to Last Row+1 of column B

tmsousa

New Member
Joined
May 14, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone!

I'm looking to compare two columns on 2 different files. Let's call them Column 1A (File 1, always has the same file name and directory) and Column 2A (File 2, shares directory with File 1).

I want to extract the unique values from Column 1A that are not present in Column 2A and paste them at the very end of Column 2A. I also want to copy columns 1B, 1C and 1D for the values that were copied from Column 1A.

1A 1B 1C 1D
XXA XXB XXC XXD
YYA YYB YYC YYD
WWA WWB WWC WWD
ZZA ZZB ZZC ZZD

2A 2B 2C 2D
XXA XXB XXC XXD
YYA YYB YYC YYD

In this case, the macro would compare columns 1A and 2A. It would then find "WWA" and "ZZA" as a unique values on column 1A that are not present in column 2A. Then it would copy WWA, ZZA and their respective values on columns 1B, 1C and 1D and paste those 4 columns at the end of my used range on file 2 (in this case, file 2's table would now look like file 1's table). If this is a difficult / impossible step, then I can just use a vlookup between both files to bring in the data from columns 1B, 1C and 1D.

I hope this makes sense, my capabilities on VBA are quite limited and I'm struggling with this one. Please let me know if I can provide further info, any help would be really appreciated :)

Many thanks!!
Tiago
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

tmsousa

New Member
Joined
May 14, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi Mumps,

I've been struggling with an issue: workbooks.open doesn't work for the first time I open the file. If I open the file and run the macro, it asks me to select the file rather than opening it automatically. Is this normal? Is there any workaround?

Weirdly enough, after selecting it once, it works automatically when I run the macro again...

Thanks a lot!
Tiago
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Is the destination workbook which contains the macro saved in the same folder as "SourceFile.xlsx"?
 

tmsousa

New Member
Joined
May 14, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Is the destination workbook which contains the macro saved in the same folder as "SourceFile.xlsx"?
It is, yes! No other files with the same name, I even wrote the directory rather than using "this directory" but it's not working. As I said though, it works perfectly after opening it manually once :/
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238

ADVERTISEMENT

That is strange. I tested the macro on some dummy files and it worked properly the first time.
 

tmsousa

New Member
Joined
May 14, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
That is strange. I tested the macro on some dummy files and it worked properly the first time.
I just changed the directory of both files to a random folder (was testing on my desktop, maybe that's an issue?!?) and indeed it works right first time...

I'll keep an eye on this and when I'm in my final environment I'll give it a go!

Either way, this thread is solved so if I encounter any issue I'll create a new one / navigate through the already existing ones.

Cheers :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top