Hi Karen,

Thanks for your kind words 🙂

The attached files don't have any data, just headings. Can you please add some dummy data so I can show you the end result of merging these 'forms' with the Master, especially since the Master 'may or may not already have rows of data'.

In regards to this existing Master data; should the User Form data be added to the bottom of the Master data, or do records get merged together, if so how do I match up the records, is there an ID number or similar present in each file?

Also, please confirm that these 'user forms' are just Excel worksheets that people enter data into directly i.e. they aren't Excel User Forms written with VBA?

Lastly, will the multiple user forms be in the same workbook or separate workbooks?

Thanks,

Mynda

P.S. the good news is this will be dead easy with Power Query 🙂

Hi Mynda,

You're the best, thank you so much for your help.

I added some dummy data.

There shouldn't be any duplicated information (CN may duplicate due to multiple requests but that's ok), also, there will be duplicating codes such as in OILN, P, Q  columns and some will be blank. So the User Forms will need to be added to the Master after the existing rows (if any). The only time record data needs to be merged together is in column "J" on the Master (Name and Comments from User Form(s)) with a limit of 20 chars.

Yes, the User Forms are filled out manually by various users, no VBA or forms.

the multiple User forms will be in separate workbooks.

Another question, can PQ validate character format, length, etc?

Thank you 🙂

Karen

P.S. I need to learn this, I feel many requests coming after this.

Hi Karen,

Thanks for putting some data in the files and clarifying the points. Yes, Power Query can validate character length. Not sure what you mean by validate format, but possibly, depending on what you mean by this.

In the attached file I've created 3 queries; one to load the Master data to Power Query, one to get the User Form files and consolidate them and one to append the Master Data to the User Form data. 

I took the following steps to get to the end result (note: these steps are only done once at set up, after that you click one button to Refresh/Update the data):

1. I formatted your Master data and User Form data in an Excel table (excluding the Title in the User Form).

2. Saved the User Form file in a folder called 'User Forms'. All User Forms will be saved in the same folder and preferably nothing else in this folder.

3. Saved the Master file outside of the User Form folder. Doesn't matter where, just not with the User Forms.

4. Loaded the Master file table into Power Query

5. Loaded the files from the Folder 'User Forms' to Power Query

6. Renamed the User Form columns to match the Master column names. This allows you to append the data.

7. Removed the Date and NPN columns from the User Form data as these aren't required in the Master. Note: none of these steps alter the original source data.

8. Append queries as new. This creates a new query with all of the data (Master and User Forms) consolidated into one Table, which can then be loaded into a Table in the workbook, or the Data Model/Power Pivot. I've loaded it to a Table in the sheet called 'Consolidated Data', so you can see the end result.

Notes:

The User Form files must be closed for Power Query to access the files

The Table name in each User Form file must be the same. e.g. UserForm

Your mapping table showed a Name column in the Master, but your example file didn't have a Name column, only Name/Comments

I hope that gives you a proof of concept. There are too many steps for me to document them all here. You can use the files provided, but you'd need to edit the queries as the file paths point to folders on my PC. I can show you where. Or, you can learn Power Query here and do this yourself.

Mynda

Hi Mynda,

thank you so much for doing this. Not quite sure how to work with it now but I managed to changed the file path of Sample File. Not sure how to run it.

Since the Master and Forms would change daily, weekly, etc. I assume I can save the query and run with new files daily/weekly.

Now there are other warnings, stating,

Not sure what that means. (attached)

Yeah I probably didn't clarify, the Master was merging the Name and Comments columns from the User Forms into one column.

I have been using Excel for years but mostly for recording data row by row, a few formula's, am still working on finishing the Extreme Power Pivot course (slow going due to time constraints). So what would you recommend, courses that is, to perform this and get the most out of my time and money?

Thank you,

Karen

Hi Karen,

My assumption is that you will have one Master file and many User Form files. These files will get added to and updated daily/weekly, as you say, and so all you need to do is click the Refresh All button on the Data tab of the ribbon and Power Query will read the files and get the updates.

If you add more User Form files to the folder then Power Query will automatically pick these up too (assuming the data is stored in Tables called 'UserForm'. This is because the UserForm query is getting All Files From a Folder, the folder being '...User Forms', it then looks at the sheets in those files for Tables called UserForm.

I see the error, but I suspect the error in the UserForms query is the issue. And I suspect the file path is not correct in the "Transform Sample From..." query. You will also need to update the file path in the Master query.

To do this, click on the query and open the Advanced Editor on the Home tab. In there you will see the file path in the first line or two of code.

I highly recommend you learn Power Query. It will be life changing. I am not exaggerating!

Let me know how you get on.

Mynda

Hi Mynda,

Thank you for your help. I was able to write 4 macros that get me to my desired results which will work in the interim. Having said that, I didn't really understand all of what you were saying and doing in Power Query and couldn't get it to work. I had to add another step to the process, which is to concatenate two rows on the original user forms to appear on the formatted file as part of the end result. Can Power Query concatenate data too?

I did sign up for your Power Query course in the meantime. But I just signed up and will be watching and practicing in my spare time.

Thank you and have a nice weekend. Happy to hear more from you when able.

Karen

Hi Karen,

Glad you found a workaround while you get up to speed with Power Query.

Yes, Power Query can concatenate rows. It requires you to transpose the data first, then concatenate what are now columns, then transpose back. But all easy and doable within Power Query 🙂

Enjoy the Power Query course. I'm confident you'll find lots of uses for it, and don't forget I'm here if you have any questions along the way.

Mynda

Hi Mynda,

Thank you. I'm excited to learn PQ and its potential to improve processes. Along with concatenate, I'll need TEXT to put zeros in front if columns don't meet the field requirement.

Was I supposed to receive an "M" language ebook? How do I get a copy?

Also, is there a transcript of the video's? It would be nice to have written copies of the steps.

I'm sure I'll have questions for you as I go through the training.

Thanks again, have a great week down under 🙂

Karen

Hi Karen,

The eBook gets sent to you 31 days into your membership. Do the course first, then use the eBook as a reference later on.

No transcripts, sorry, but you can watch the videos as many times as you like, and as I mentioned, use the eBook as a reference.

Mynda