PDA

View Full Version : Copy data from Multiple WBs without Duplicates



swaggerbox
02-23-2017, 05:42 AM
I have a over a hundred workbooks in "D:\MyReports", each with the same format: (a) column A is a list of all publication dates, (b) column B is a list of all unique reference numbers, (c) sheet name is "source". I need to copy all the unique column B values and their corresponding column A values to a master workbook (sheet name "master") located in another folder: "D:\Master". The hitch is that there are cases where the reference numbers (listed in column B) are sometimes repeated in another workbook, so that a duplicate is created in the master workbook. How do I copy only the unique reference numbers in column B (and their corresponding publication dates - column A) to a master workbook without repetition?

Column A Column B
12/1/2016 1234345
10/3/2016 1345566
10/3/2016 1344559
10/1/2016 5391231
10/2/2016 4856066

p45cal
02-23-2017, 12:40 PM
Copy all the rows over, then when finished use the built-in Remove Duplicates.

swaggerbox
02-23-2017, 03:59 PM
hi p5cal, isn't there another way? there is a lot of data to process. over a hundred workbooks. and there are two columns.

p45cal
02-23-2017, 05:08 PM
isn't there another way? there is a lot of data to process. over a hundred workbooks. and there are two columns.
Probably, but it would involve a lot more coding. How many rows per workbook? 7k?

swaggerbox
02-23-2017, 06:05 PM
there are many like more than 15k

p45cal
02-23-2017, 07:24 PM
100+ workbooks x rows in each workbook > 1048576?

Your looking to eliminate duplicates of just column B values, or combinations of duplicate column A with Column B values?

swaggerbox
02-23-2017, 10:06 PM
i am looking to eliminate duplicates of just column B values. The output should be the unique column B values and their corresponding column A values.

swaggerbox
02-23-2017, 10:30 PM
i am looking to eliminate duplicates of just column B values. The output should be the unique column B values and their corresponding column A values.

JBeaucaire
02-28-2017, 08:31 AM
I'm not sure how any suggestion would improve on the one give in post #2. That will be a single line of code added to the end of your existing macro that will execute pretty fast, certainly faster than running numerous checks line by line over 100s of workbooks to insure the duplicates are not copied in the first place.

I underscore the REMOVE DUPLICATES is certainly the fastest path to the solution for this question.