Data Merging in 1 Sheet from 100 WB - New to the FORUM and Brand New to VBA

[spiwere]

Good morning!

I posted this on another Forum. Here's the link: http://www.ozgrid.com/forum/showthread.php?t=173484

Unfortunately, did not get a response. Have heard that posts here get answered much quickly and so I'm looking for your help!

My request is as follows:

I’m currently using Ron de Bruin’s Addin to merge / compile my data set. The only challenge that I have with it is that it doesn’t merge the data when the 4 tabs of sheet have headers in different orders.

Attached is a sample sheet of what my data sheets look like (Column are always the same) per sheet:

o Sheet 1 = 13 Columns
o Sheet 2 = 16 Columns
o Sheet 3 = 13 Columns
o Sheet 4 = 14 Columns


I have provided a mapping of where the data is to be placed in the tab named “Data Placement” on compilation.

This is my first ever post on this forum and just hoping that I have stayed within the rules.

Looking forward for your proficiency in helping me conclude this challenge.

Could anyone help me resolve this using an Addin or a Macro. Hope I’m not missing a trick on Ron’s Master piece I got from this link: http://www.rondebruin.nl/merge.htm (May be this link is useful for other who aren't aware of it)

Thanks....

[nilem]

Hi, spiwere
What if you try to solve your problem in two steps?
step 1: data collection of 100 books in 4 sheets of the resulting book
step 2: form a report from 4 sheets of the resulting book, as you need.

[spiwere]

That should work! If I can get a code that is capable of executing it.

I Lookout for you help!

[nilem]

Can you show two or three examples of books with the original data and an example of the resulting report?

[OllieB]

@Spiwere,

Your requirement is not really clear to me:
a) does each of the 100 WB's contain four sheets or 1 or more sheets?
b) how would the sheet types of the input workbook be recognised;
c) upon import into the master workbook, do sheets 1 to 4 need to be populated or are you just interested in the final result (= Data Placement?)

[spiwere]

Sorry for not being clear!

A) Yes, Minimum 1 and Max 4 sheets
B) Sheets have a fixed name. If that's what the Q2 refers to
D) My final requirement is the Data Placement sheet.

Thank you for your interest to help me.

Do let me know if you have any more queries.

[OllieB]

I am thinking of a generic solution whereby in a comment for each cell in the header row on the destination sheet we specify the origin of the values to be written per name of the source worksheet. So for example we could agree on a notation like sheet1:A, sheet2:B,sheet3:*blank,sheet4:A. This would be processed as: if the row comes from a worksheet named sheet1 then the value should come from column A, if the source worksheet is named Sheet2 then the value should come from column B, and for Sheet 3 the column should be left blank. By using the comment objects on the header row we could build a dynamic and generic approach whereby the import/merge could be merged without further changes to the code. How does this sound to you?

[spiwere]

Makes sense to me. Just one thing though - will I be able to identify the source for each row of data as we merge all of it one. That will help me check when necessary.

Thanking you for all the help.

[OllieB]

We could either create a separate worksheet that logs every record imported or we add three preceding columns on the target worksheet for the name of the source workbook, worksheet name and row number on the source worksheet.

[spiwere]

That would be an icing on the cake - Adding preceding columns on the target worksheet!

WOW!

[OllieB]

If you want to proceed with this direction then please provide an example target worksheet with meaningful column headers and two examples of workbooks to be imported (with column headers and proper worksheet names and one or two data rows - may be fictitious data).

I would also like to know how you would like to select the workbooks via (a) a dialogue allowing the users to identify/select the workbooks or (b) import all workbooks from a directory to be specified/selected by the user.

Finally can you give an indication of the average number of rows per workbook to be imported, so I have some feeling towards the performance, overall duration and need for progress messages to the user.

[spiwere]

Absolutely!

1) Give me some time I'll have the sample files up.

2) Import all workbooks from a specified directory would be the best option.

3) Average Number of Rows per workbooks - Now this is a tough one. Varies quite a bit. Don't know if I can provide a rough estimate for it.

Samples coming up shortly...

Thank you!

[OllieB]

Ok.

Does not have to be a real scientific average. Are we talking about 10 rows, 1000 rows, 10000 rows per workbook?

Ps I have moved away from using the comment object to specify the mapping but will offer a separate mapping definition worksheet. Much easier to view the overall assignments, and changes would also be easier to make. Trust this is ok.

[spiwere]

OllieB,

Apologies, I'm facing some power issues. Will it be okay If I can share the files in a few hours. Once it's up and running. Actually, one of the transfers just blew off

[OllieB]

OllieB,

Apologies, I'm facing some power issues. Will it be okay If I can share the files in a few hours. Once it's up and running. Actually, one of the transfers just blew off

Don't worry I wont need them until tomorrow morning CET time.

[spiwere]

Hi OllieB,

Sorry for the dealy!

Here are the required files:

Data Sheet A = Raw Data File 1
Data Sheet B = Raw Data File 2
Output File = The way the data needs to be merged.

Please do let me know if you have any questions. Hope I've provided all the details clearly. Thanks so much

[OllieB]

Spiwere,

Have started. Noticed that the column_header_names between the combined worksheet and those on the import worksheets are identical. How sure are you about the correctness? If 100% I can make the entire process dynamic (i.e. match on column names) and there would not be any need to define or even maintain a mapping.

[spiwere]

That's true! All column have to be exported and are with same headers with the exception of sheet 2 where the 2 address columns have 1 and 2 in the end. But have to be eventually aligned with the address columns in the Final Output sheet. Hope this makes sense!

Sorry about the late reply was driving, just saw your note so stopped by to answer.

Thank you!

[OllieB]

spiwere,

I have built a first/draft version. I think the coding is OK. Due to the 'requirement' for row numbers from the source worksheet the approach is to copy the data row-by-row and column-by-column as opposed to copy & paste column-by-column. The latter is the quicker approach but then I would be stuck with a loop to write row numbers into a column.

I have tested the solution with the data provide by you but noticed some discrepancies in the mapping and even between the two source workbooks. For example


Workbook A, Sheet 1, mismatch column EMPLOYEE COUNT and APPOINTMENT TIMED OUT – column header in sheet 1 incorrect?

Workbook A, Sheet 2, GIST_CODE not mapped in example provided?

Workbook A, Sheet 2, mismatch EXECUTIVE_SPONSOR and FOUNDER_NAME column headers. Entry in column FOUNDER_NAME is mapped to EXECUTIVE_SPONSOR in example provided

Workbook A, Sheet 3, column URL holds employee count?
Workbook A, Sheet 3, column EMPLOYEE_COUNT holds YES/NO values?


The worksheet Combine Sheet Backup contains some of the debugging/testing performed by me. Here you will for exampel also see that workbooks A and B differ. For example employee_count in A is filled with Yes/No while in B it contains the proper employee count.

If a higher performance is needed, we could discuss dropping the source_row_number column OR adding a row_number_column (or key column) to the workbooks/worksheets to be imported.

[spiwere]

Thanks Ollibe.

The mismatch happened because the data is entered manually and i picked the original dump sent by my vendors. We need to celan it later after merging. Sorry about that but wanted to share the exact data.

And yes, we could drop the source_row_number column OR adding a row_number_column (or key column) to the workbooks/worksheets to be imported. If it speeds up the process.

Thanks again!

[nilem]

as an option
previously unpack zip archive to a separate folder on the disk

[OllieB]

Thanks Ollibe.

The mismatch happened because the data is entered manually and i picked the original dump sent by my vendors. We need to celan it later after merging. Sorry about that but wanted to share the exact data.

And yes, we could drop the source_row_number column OR adding a row_number_column (or key column) to the workbooks/worksheets to be imported. If it speeds up the process.

Thanks again!

Ok, just run the process, and let me know whether it takes too long, and than I will create an alternative approach.

[spiwere]

Dear

This is going to be very useful. I ran it on 40 files, and did not take much time. I daily spend about 2-3 hours on. It could be because that the data wasn't huge. Thanks a lot! I think we can keep it the way it is for now. I'll seek further help on modification if I get stuck with size issues or come across a problem.

Just one quick question though - I do several other data compilations like these on a daily basis. In case the headers for those reports are different...

Where all the changes will need to be made in the exsisting code? My guess is: Combine Sheet backup - Row 1 and MAPPING sheet - Row 1.

It will help me automate the other stuff as well

Wonderful is the word! and Thanks would be much less for this BIG help!

[OllieB]

spiwere,

Ok, glad to hear it wroks for you. Please remember to mark this thread as SOLVED, and to click the star icon if you are happy with my contribution

The combine sheet backup is just a worksheet I created to validate the process and coding- no changes needed there! In fact you can delete this worksheet from the workbook.

If you need to amend the column mapping, or introduce new mapping (for a new worksheet named for example Sheet5), the only change you need to make is add/change the MAPPING worksheet! Everything has been coded dynamic/generic so you can add as many mapping definitions for different worksheets names as you want, as long as the worksheet names are unique. Also adding additional columns will not be a problem just as long as the column header layout on the MAPPING data worksheet matches the one on the Consolidated Output worksheet.

[spiwere]

Thanks again. I'm immensely thankful. I'll surely go ahead and mark this one as "Solved"

I did try changing the headers on the MappingSheet and also changed the worksheet names that are consistent (except that some workbooks have 1 and some have 4 sheets), but got the following error message on clicking import: A problem occured when retrieving the mapping definition for MAPPING, process terminated. I'm sure the Columns have been mapped accurately.

Please advise!

[OllieB]

That means that one of the workbooks you are trying to import contains a worksheet named MAPPING for which you have provided no mapping definition. I think you are trying to import the master workbook into itself? I suggest you place the target/master workbook in a different directory than the workbooks to be imported.

[spiwere]

Hmmm...I'll give it another shot and comeback by tomorrow. In the meanwhile, marking this one as "Solved"

Thanks a lot

[OllieB]

Else, feel free to post a copy of the MASTER workbook and I will have a look at it.

[spiwere]

Thank you so much! You are immensely helpful.

Let me give it another shot. Guess I'll learn that way, but If I get stuck I'll surely send it to you later in the evening tomorow.

Thanks again

[spiwere]

Between the last post and this one I ran it again it says "import completed, 0 rows were imported" What do think the error coud be due to?

It did, It did...Yahoo...It worked. Some slight tweaks might be required. I'll review and comeback. Ignore the above line. I'm super happy!

[OllieB]

On my way home in the car I realized that I forgot to code a workbook close instruction in the loop. This may cause memory problems. You will receive a new version either tonight or tomorrow morning

[spiwere]

No worries and no hurry.

Just one piece of additional advice I need for...say if the data starts in each row of all the workbooks at row7 where all do I need to change the code. In my other versions, it is the case and so the column labels like from RDBmerg from all sheets are getting added again and again.

[OllieB]

spiwere,

new version attached!
- mapping worksheet now has option to specify the data start row per worksheet mapped so no code changes necessary when this changes or becomes different per worksheet!
- added the instruction to close workbooks imported

[spiwere]

Dearest OllieB,

Absolute genius I must admit! No words are enough for this help.

I'll give it a try and seek any further advice.

Thank you!

[spiwere]

Dearest OllieB,

I'm home too. Just ran it again, but getting the following message "import completed, 0 rows were imported" What do think the error coud be due to?

Thank you!

[OllieB]

Difficult to say without knowing the mapping, having access to the workbooks etc. It could also be a mistake in the most recent version. I did make the changes at home and did not test everything. Let me quickly setup the test environment and run a test over here. I will get back to you.

[spiwere]

I understand that...but it seems that it is more to do with my version. I'm using xls. Do you think it could be the root cause? I ask this because when I change the files to XLSX it works. Thoughts please...

[OllieB]

Ran the test. One minor error message at the end (after the import has completed). It tried to close the last workbook imported, but the workbook was already closed (minor revision attached), so no big problem. More important though, the import worked flawless. All data was imported in the correct manner.

Now to your problem. The final message is based on the total number of rows present in the consolidated worksheet -/- the number of rows present before the import. If the message states zero records imported, then no records were imported. Two possibilities, did you select the right directory and were *.xls* objects present, and possibility (2) did the import workbooks contain data past the start row specified by you in the mapping worksheet?

[OllieB]

I just saw your post #37. I will change the like statement used to identify workbooks. Attached another revised version. Please test again (with .xlsx)

[spiwere]

Dear OllieB,

1) I'm selecting the correct directory with the xls files available in it.

2) Yes, the data is past the 7th row.

Guess I may be doing something incorrectly with the mapping. Do you think that could be another reason? If yes, then I may need to take a look again tomorrow.

Thanks!

[OllieB]

I think our posts are crossing each other. Please test with version 2.2 and confirm whether the problem persists.

[spiwere]

Yes, Indeed our posts are crossing each other

I did run it again using 2.2 but the problem persists I'm pretty sure the mapping is correct. The other report I'm trying to automate using your masterpiece has exactly the same number of columns. The only difference being the column labels that I've changed.

No other changes, my guess is that it is something to do with the version. What do you suggest?

[OllieB]

I found the problem, by taking the two data worksheets and saving them as 2003 versions.

It is a weird one.

I used the following code to determine the last row on the data worksheet

Please Login or Register  to view this content.

this works fine for 2007/2010 worksheets but NOT for 2003 versions (you must not have 'break on all errors' set, otherwise you would have received an error message)

I have now replaced the coding with

Please Login or Register  to view this content.

and added a check to only process rows with a non-blank value in column A.

Anyway on my side it now works.

[spiwere]

It is to do with the version. Is it possible for me to share the file via email? May be you can have a look and see why isn't working. I can send one without any data. Will that be okay?

[OllieB]

Did our posts cross again? I found the problem. Did you test with 2.3?

[spiwere]

No it didn't this time! I did try but same problem. I had seen some other posts as well and people had similar issues as mine, but haven't seen a real resolution that was available on the Forum. May be I'm missing anything as I'm absolute new here

[OllieB]

Ok. I tested on my PC with both 2003 (.xls) and 2007/2010 (.xlsx) objects. All of them get imported properly with version 2.3. I guess there is no other way then for you to post the master workbook and at least two import workbooks which are not properly imported. Otherwise this is turning into a guessing game

[spiwere]

You are right! Can you give me time time till tomorrow evening or max day after. I'm traveling tomorrow, and may not be able to access my system that much.

Appreciate all your help with this

Cheers and Good night!

[OllieB]

No problem

[OllieB]

spiwere,

I think I have identified the problem. It is indeed related to processing (.e.g. opening, reading) workbooks of type 97/2003 in a 2007/2010 code environment. The issue encountered is quie interesting, and for the sake of others I am describing it below.

I was using the following statement to determine the last row using in column A on a worksheet

Please Login or Register  to view this content.

This code is running in a 2007/2010 workbook and works fine when the opened wordbook/worksheet is of the same version. When I tried to run the same code on an opened 2003 version I received an application error (1014), even though running the exact same statement in a manually opened 2003 workbook works fine.

After some debugging I found that Rows.Count returns the maximum number of rows available in the workbook where the code is running. So in our situation it would return 1048576 which is valid for 2007/2010 worksheets, but NOT for 2003 worksheets. There the maximum value is 65536 which you automatically get when you open a 2003 workbook in Excel and run the commend from the immediate pane.

The solution therefore is to use the amended statement (which will now always work, irrespective of the version of the worksheet)

Please Login or Register  to view this content.

The revised version is attached. Please test with version 2.3.1 and in your feedback mention the version number so I know you used the correct version.

[spiwere]

Thanks OllieB!

Really, really appreciate your assistance on this… 2.3.1!

On second thoughts (I know I did say I only need the consolidated report), but is it possible to add some trick to be able to additionally have the data compiled by tab as well. It will enable me to deal with the data tab wise and allocate for cleansing purpose. I would still want the consolidation to carry on in one sheet, as this is working like pure magic.

Only if that isn’t too much of an effort.

Thanks again!

[OllieB]

Have a look at the attached workbook. Notice that on the MAPPING worksheet you can now specify the name of a COPY_WORKSHEET. When not blank, all data copied onto the consolidated worksheet for the source worksheet will also be copied onto the worksheet specified.

[spiwere]

Amazing stuff! Thanks a lot OllieB… I’ll give it a try and comeback in a bit.

1 quick question: As my sheets have different columns Sheet 1 has 13 and Sheet 2 has 16. Will I able to combine data in the Version “Output File OllieB V2.4 “ for only those columns that are actually present in the respective impSheet3?

What I want to be able to do is that in the impSheets (1-4) compile data only for those columns that are present in the original sheets. Please suggest and we are all set then....

You are a rock star…

[OllieB]

I am not sure that I understand your new requirement. Can you provide an example?

[spiwere]

Sorry! OllieB.

I think I wasn't clear. My apologies.

My question was that since my data sheets have different number of columns. One has 13, second has 16. So I wanted to know if in the impsheets will be able to collate data for only those columns that are available in the base workbooks from which the data is getting imported or will I get all the columns as in the main compile workbook.

Please let me know if it clarifies.

[OllieB]

So are you saying you want impSheet1 to have the same columns as import Sheet1, impSheet2 to be the same as import sheet2, etc

[spiwere]

Exactly! The respective sheets i.e. Impsheet 1,2,3 and 4 only have data columns that are in the source files from which the data is being imported.

The consolidated sheet has all the columns, as is now.

I'm all good with it then.

Thank you.
Spi

[OllieB]

One more question. Do you also want to have the workbook_name column and row numbers added on the left (Sheet name would not be relevant), or just the actual source data? In any case, I can only deliver a new version (2.5) by tomorrow morning CET. I do not have the time this evening as my wife is complaining that I am spending too much time on this forum

[spiwere]

Workbook_name column- Yes please. Row numbers added on the left - Only if its not to much of a hassle.

No worries, about the delivery. Please take your time. I'm immensely thankful.

By the way, my wife is feeling the same here

I'll look forward to this final bit.

I've named my WIP version as "Masterpiece by OllieB"

Thanks and Good Night!

[spiwere]

Hope you are doing great OllieB!

Please let me know in case you have any questions.

Thank you!

[OllieB]

The new version is ready and tested. Since we now also make a full copy from the import worksheets I have decided to re-write to copying logic. My tests with the new version run faster. Unfortunately the corporate network connection to the Internet is down so I cannot post the new version. This post is written from my iphone

[spiwere]

Thank you so much!

I’m eagerly waiting for it to show its miracle…

You are the besttest! Never imagined my first post will be that “Big a Success”

[OllieB]

Here is the latest version.

[spiwere]

Superduper!!! You are a real hero. Thanks for making my first post a grand success!

Forum members - Please join me in thanking Ollieb for all his help and expertise! You are simply awseome:-)

Kind Regards...

Spi

[spiwere]

Just wanted to convey my thanks again OllieB! Your solution rocks!!!

You've made my life damn easy man! Cheers!!!

[OllieB]

You are very welcome! It is nice to see somebody that enthusiastic

[spiwere]

Thanks a ton OllieB!