Combine multiple spreadsheets, using common columns and combining any duplicates

Hi, I am trying to figure out how to combine the spreadsheets on the attached workbook into 1. In the example attached I only included 1 or 2 items from each spreadsheet because I needed to change some info to attach it here. But,
the columns data in the rows on each spreadsheet can range from 0 to a couple thousand. I need for the columns with the same headings to be all under the same headings.. as it is now each spreadsheet may have the column names in different areas, and each one may have a column unique to just that sheet. So, the final spreadsheet would need to include all of the columns listed from each report (1 of each).. the other variable here is column A, the file number, if the file number can be repeated in the spreadsheets, but I need for the combined spreadsheet to only list each file once (and adding the data from that particular sheet) Some sheets do not have data, for that particular day, in that case, cell 2A = "No matching account found" - and that data does not need to be transferred.

Any help would be very much appreciated, thanks!

BG1983,

Here's how I read your problem.

Please Login or Register  to view this content.

Hi, yes, that is the only common values, so, it would be one
Row with both of those dates filled in on those two columns
.. while the rest of the columns are blank...

Jindon, i am not in front of a PC right now but look
Forward t giving this a shot. You have helped me
Tremendously a couple times! You see to be great at
Solving these problems extremely efficiently so i
Was wondering if you taught any courses, or, how you
Learned... the style of your coding is awesome and something
Id like to understand a bit more. Again, truly appreciate
Your help and will let you know how this code works for
My purposes

Okay - So I think this is what I needed. But, something I didn't think of is that it would be great if we made the name of each tab a column in the "Summary Report" Sheet - Preferably Column A.

Also, something I just noticed is that the columns "Assigned Attorney" and "Attorney Name" are separated - which is correct based on my description since they are not the same name. But, if possible, they should be treated as the same - which would be "Assigned Attorney"
The same logic would apply to the columns "Attorney Review Date" and "Attorney Reviewed Date"

Thanks again for your help!!

The I need to see the result that you want in workbook.

Just 2 or 3 examples clearly shows the logic will be enough...

Sure - Here you go. FYI - I changed the fill of the columns that are repeated but with different names to red so that they stand out... you can ignore the fill otherwise

Not sure if this is how you wanted...

Please Login or Register  to view this content.

Just about! Everything looks good but when I ran this on the actual data the "Current Bucket" column came out all blank. (This is the one that should be named as the tab that the info came from - and if the file number is repeated.... where you have combined the data so that is is only on 1 row.. its fine that the second "current bucket" overwrites the first "current bucket" in that column..)

Can you just add one line

Please Login or Register  to view this content.

You're my hero man. Looks great! Thank you for your help!

So I am attempting to use this for all workbooks in a folder now... Do you mind taking a glance at the below and see where I am going wrong please?

Please Login or Register  to view this content.

Untested.

Please Login or Register  to view this content.

Hey, so I couldnt get this to finish with the full number of workbooks I had in the folder.. i let it run for like 45 minutes but still hadnt finished. But, I used just a couple workbooks and it looks like it worked.

The one last thing I'd like to have happen is for the workbook name that the data came from be the value of a column A. So, all tabs in "Workbook1" would have a value of "Workbook1" in column A of the Summary Report tab of the workbook I ran the report from. Then column B would be file number... and C would be Current Bucket

No sure, if this works

Please Login or Register  to view this content.

Thanks, going to try that now.. it takes a while to run. Out of curiosity - Do you think that this would work better for me if I first had all of the tabs from each workbook transferred onto one workbook... and then ran your portion of the code? Instead of doing it all at once?

Fully depends on how many workbooks/worksheets and # of rows in one sheet.

If you could copy all the worksheets in all the workbooks, it might be better...

Well, the workbook I provided at the beginning as an example likely has the most worksheets out of all of them.. there are around 17 workbooks total... the total "fileno" population (all files in our inventory) is around 100,000.. so, as long as there are no duplicates at the end there should be around that many rows. The most rows in 1 worksheet is around 10,000 I believe.

Just trying to figure out the best way to get all of the data from each workbook, by sheet... (including both worksheet name and workbook name for all) into one table...

Also, was thinking that it would run smoother if I changed all of the files from xlsx to xlsb...

So when I tried this one it immediately returned with a message of "no data"

That means every worksheets in workbooks may

Any one/both of header "FileNo" or/and "Client Name" is missing. or A2 is ""NO MATCHING"....

Did you set it up so that it always had to have both the Fileno and client name in there? These have fileno but not client name..

My code is assuming that "FileNo" and "Client Name" makes unique key...

The FileNo alone is unique

Then try change to

Please Login or Register  to view this content.

hmm... now it says runtime error 13 - type mismatch

OOps
change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Okay, so aside from FileNo not being in column A, that worked! So, going back to the workbooks, adding all worksheetes to 1 workbook... I just used the recorder and selected all sheets, then copied over to other workbook... like below.. is this the best way to do that? And would there be a way to add the workbook name anywhere in there? Sorry, I promise I am almost done bothering you!

Please Login or Register  to view this content.

Does that mean all of the worksheets in all the workbook?

The above? That was only for 1 workbook. But yes, I am trying to get all of the worksheets for all of the workbooks onto 1 workbook and then combine them into 1 worksheet like your code does

How about

Please Login or Register  to view this content.

Hmm, stopped after 1 tab with error "subscript out of range"

Dang, its because both of the tabs are named the same thing.. which I think may be the only time that happens... can a 2 be added if there already is one?

Nevermind.. I removed that workbook and re-ran it and it threw the same error

Missed one line,
It should copy regardless of same worksheets.
It shoud rename something like "xxx(1)" "xxx(2)" and it is working here.

Please Login or Register  to view this content.

Same error :/

I had added the exit_sub: previously..

Okay - I got it. My open workbook was starting on sheet1 - not 2.. so I changed that and we are good

Thanks again for your help man!

So there are 240 worksheets haha... whats the best way to delete all of these after they have been added to the new sheet? One at a time or all at once at the end?

This I assume...?

Please Login or Register  to view this content.

Something like

Please Login or Register  to view this content.

Gotcha. Okay. Well, I tried to run it on the 240 tabs and got an "overflow" error.. so, going to take a break for a bit and think things through... i assume overflow would just be because its too much.

I guess so and my next reply will be tomorrow.

haha - again, I appreciate your help man - Have a good one

Hey, so I know this is off topic, but wanted to borrow your genious to glance at this for a second if you had a minute... this is a macro that runs off of a refresh-able spreadsheet. If the user selects this macro, it will pull up the date 2 input boxes for the date range.. then it filters the table.. then copies the data over to a new spreadsheet called "Who's On First Data" then it cleans up that spreadsheet a little bit before creating a new tab with a pivot table called "Who's On First" -- the pivot table is the days of the week that the user selected, with everything that is not blank filled with the color red.

My problem is that it works once.. then it wont work again... after deleting the tabs.. etc.. have you seen anything like that before?

Please Login or Register  to view this content.

BG1983,

Sorry I was away.

It is too hard to comment without a workbook...

any luck with this?

BG1983

Still not in good condition, but how your last question related to your original?
I think you should better open a new thread.

Okay, done - Its here: http://www.excelforum.com/showthread...02#post4411302