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

[tmsousa]

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

 

[mumps]

You are very welcome.

 

[tmsousa]

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]

Is the destination workbook which contains the macro saved in the same folder as "SourceFile.xlsx"?

 

[tmsousa]

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]

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

 

[tmsousa]

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

 

[mumps]

Glad it worked out.