Auto populate to master worsheet from multiple workbooks and...

I would like to make a master worksheet that auto populates with the work some employees save to their own workbooks. I presume this involves make identical column names in a new workbook but I am lost moving forward as this is not my area of expertise.

I need to make sure the clients are not being duplicated on the list, I simply want each client to have their own row and as my coworkers collect their info the empty cells with corrosponding column names start filling in.

Ive searched the threads on auto populating but I dont see anything that addresses this particular scenario... Any one have ideas or places to point me?

Thanks much,

A long time lurker

I want to paint a everyone a clearer picture....... This is what im looking for.

Workbook 1

Client Name Info 1 Info 2 Info 3

Workbook 2

Client Name Info 4 Info 5 Info 6

Workbook 3

Client Name Info 7 Info 8 Info 9

Master Workbook

Client Name Info 1 Info 2 Info 3 Info 4 Info 5 Info 6 Info 7 Info 8 Info 9


-Rules

1) I want the master to be populated upon the other workbooks being saved
2) The workers enter new clients daily so they should simply be added to the bottom of the masters list; unless of course that client already exhists in the master. (in that case I just want it to populate the cells of the exhisting client name in the master.

***The client names are not in the same order from workbook to workbook***

Hi ken

One of us could "wing" it and provide untested code for you (and for us) to struggle with and debug. If you want tested code that's been demonstrated to work, please post samples of your workbooks with data that represents your "real" data (including the actual file structure...same number of columns). Include a mocked up example of the "Master" that demonstrates desired results.

We'll see if we can help.

Hi ken

Is Client Name the only link between all files? No SS number or claim number?

Originally Posted by jaslake

Hi ken

Is Client Name the only link between all files? No SS number or claim number?

Yes and No...

There is a "Code" we use for each client which I could easily have each of our workers start including on their sheets in a new column. They are 6 digits long, Michael Thorton would bee (THO001). If there were two clients with the same name the next one would be (THO002). I dont know if I could ask them to write them in for all past clients entered however. Im guessing you see a need for a completely unique number to each row so there are no issues with overwriting?

Lets just add a column to each of the 3 worksheets called "code", I can have them put it in Column A. Not sure if I can get them to add it to the work they have already done, it would be very tedious. But they could start doing it today...

Hi Ken

I've got this somewhat surrounded (or it has me...not sure) with your original files. I'll continue to work with those files 'till I have it doing what I think needs done. The rewrite to incorporate the client code won't be difficult and I believe will be more bullet proof.

Get back to you.

right on, eagerly awaiting what you have...

Hi Ken

As I'm working thru this...just had a scary thought...do you have more that 3 "Worker" sheets?

Tell me "NO" or show me what they are and the fields that'll be pulled from them.

Hi Ken

Regarding this

Are you saying that if we ever add a 4th person it will be a total pain

It depends...if the Worker x sheet layout is the same as one of the others it's simply a matter of cloning the code. If the layout is different...then yes...quite a pain...code would need to be written specific to the layout.

I'm attaching 4 workbooks. The code resides in the 3 "Worker" workbooks...there is no code in the "Master" workbook.

Here's how it works:

• The code assumes all 4 workbooks are in the same folder.
• For each "Worker" workbook the code is triggered by a "save" event or a "close" event.
• Upon one of these events "Master" workbook is opened and relevant data is transfered to Sheet1 of the "Master" and the client is marked as processed by placing an "X" in the last column to the right and the "Worker" workbook is saved and closed. Master is sorted by client name.
• If the worker were to change certain data for a client the processed flag is removed and that line item will be updated in "Master" upon "save" or "close"
• The relevant cell changes that trigger clearing of the processed flag depends upon which "Worker" worksheet is being changed. For example, if Worker 1 were to change a cell in column A or column C the flag for that line would be removed. For Worker 2 the relevant columns are columns A and C through D and for Worker 3 columns A and C through H.

If "Master" is a shared workbook I don't know how to handle it if more than one Worker is trying to access it. You've got a lot of "stuff" going on here...I've debugged as best I could...let me know of issues.

John,

Thanks for what you've posted! This is excellent...I wish I could return the favor!

I do see a few minor things that need revision as im playing around with it on our shared drive and with real data. I'll make a short list of them before I start asking for help with changes...

Originally Posted by jaslake

Hi Ken

Regarding this It depends...if the Worker x sheet layout is the same as one of the others it's simply a matter of cloning the code. If the layout is different...then yes...quite a pain...code would need to be written specific to the layout.

I'm attaching 4 workbooks. The code resides in the 3 "Worker" workbooks...there is no code in the "Master" workbook.

Here's how it works:

• The code assumes all 4 workbooks are in the same folder.
• For each "Worker" workbook the code is triggered by a "save" event or a "close" event.
• Upon one of these events "Master" workbook is opened and relevant data is transfered to Sheet1 of the "Master" and the client is marked as processed by placing an "X" in the last column to the right and the "Worker" workbook is saved and closed. Master is sorted by client name.
• If the worker were to change certain data for a client the processed flag is removed and that line item will be updated in "Master" upon "save" or "close"
• The relevant cell changes that trigger clearing of the processed flag depends upon which "Worker" worksheet is being changed. For example, if Worker 1 were to change a cell in column A or column C the flag for that line would be removed. For Worker 2 the relevant columns are columns A and C through D and for Worker 3 columns A and C through H.

If "Master" is a shared workbook I don't know how to handle it if more than one Worker is trying to access it. You've got a lot of "stuff" going on here...I've debugged as best I could...let me know of issues.

After trying as many things as I could to test it, here are a few minor things I see.

1) The worker #3 workbook does not remove the "X" upon revising a cell in a client row, unlike the other two workbooks which do. This led to the revised info not being updated in the master.

2) After the workers save or close their workbook the master opens, is updated and requires the worker to save and close it. I would prefer it auto-saves and auto-closes the master so that the user doesn't have this extra step to do.

3)Question: I have no problem with the "X" column being displayed on the spreadsheet however is it coded to the specific column they currently reside in on each worksheet? I ask because Worker #3 actually has 10 columns (A-J) of data he currently types in. (The worksheet you created puts the X's in the 8th column(H). If this is an issue the X's should go in column (K) as that one is the first empty column on the real workbook. (If its not an issue i am going to insert a few extra columns in the worksheet you made him so I can move his data in i and have it keep its original order.

Thanks again~ Ken

Hi Ken

This is a rather simple fix

I would prefer it auto-saves and auto-closes the master so that the user doesn't have this extra step to do.

This too is a simple fix. I thought I debugged it but will look at it again

The worker #3 workbook does not remove the "X" upon revising a cell in a client row, unlike the other two workbooks which do. This led to the revised info not being updated in the master.

Regarding the "X" column...I'm going to move it way to the right (probably Column "Z"). This way it'll be out of sight and out of mind...it's only used by the procedures and the user need not see it. The column ("Z") can be hidden if you like.

You can do this by giving me "style points"

I wish I could return the favor

I'll get back to you.

Hi Ken

The code in the attached appears to accommodate this

auto-saves and auto-closes the master

This appears to work as expected

worker #3 workbook does not remove the "X" upon revising a cell in a client row

Please be aware that in Worker 3 the "X" gets removed only when Columns A or C through H get changed as we're not using the data from the other columns except column B. If Column B gets changed then we have other issues.

The "X" column has been moved to column Z on all Worker sheets. Let me know of issues.

John,

As this data is populating im seeing a very obvious trend. About 50% of the clients are leaving blank cells because they arent being entered by more than one worker. This is fine but there is one column, date of birth, that can be filled in. We have a spreadsheet that has every client name and DOB we have ever had... So im picturing this.

Upon saving Client List Workbook it searches for matching names in Master (In column A for both workbooks)
Matching names pull DOB from Client List found in column K in Client List
DOB is put in Masters column B
Closes

I would say it should search by code in the client list but alas, they are not there. Every name in the client list is different however. We put middle initials, periods and other things to differentiate. This should be okay because the workers now know to enter the names exactly as they appear on the Client List.

Wadduya say? Could you help me again and write a code in a blank workbook that does this when its pasted in the Client List workbook and then placed in to the correct folder? Im thinking it should run upon the client list workbook being saved just like worker workbooks.

Hi Ken

I can visualize how this may work...although "Name" is not very reliable. I'd probably search from "Master" into "Client List" since "Master" will be the smaller workbook and you'd need only search for those "Master" records that DOB is blank.

By the way

Matching names pull DOB from Client List found in column K in Client List
DOB is put in Masters column B

"Masters" column C is the DOB column...not column B...unless things have changed since this morning.

I'll need you to dummy up a "Client List" workbook that has the same file structure as the "Live" file. You'll need only to include the records that are in the "Master" we've been working with. That should give me enough data to play with.

A potential issue...if Worker 2 (where DOB lives) makes a change to a record and the DOB is NOT present in Worker 2 but IS present in "Master", "Master" will be over written with an empty DOB. Hmmm...need to think about that.

Send me the file...we'll see what we can do.

Im being told I can create a client table that has just CLIENT NAME, CODE and DOB. Let me figure this out and ill get back... Were are using a query function in an internal software program that exports to excel...

And disregard those .xls attached, I did see how I could remove them..

Hi Ken
Let me know how I can help.

Originally Posted by jaslake

Hi Ken
Let me know how I can help.

Alright here it is, I will explain in the simplist and fastest way I can... We use an internal software program that I can query information from but not always multiple points of info I need in one query. VERY ANNOYING.

This is the first part, I can put two querys in one worksheet like so. (I will refer to this workbook as Client Data.

1) I need to compare each row in column A against each in colum D and if a match is found move corrosponding match E (birth date) to corrosponding match C (blank).

I have two lists of names. About 5000 poeple in one and 4500 in the other. It would be nice if there was a button up top that I could simply push after pasting in updated lists that i've queried.

------A------------------B-------------------C-----------------------D--------------------E
CLIENT-----------CODE------------BLANK-----------CLIENT-------------D.O.B.
Doe, John------DOE001---------BLANK------------Smith, Mike-------10/5/77
Smith, Mike----SMI001----------BLANK------------Turney, Kate------5/5/82
Carlson, Sam--SAM001--------BLANK-------------Herd, Gale---------12/7/79
Style, Bill--------STY001---------BLANK------------Depot, John-------1/31/55
Kerr, Greg-------KER001--------BLANK-------------Doe, John-----------5/5/65

If this can be accomplished I will have the client name, birth date and client code matched properly and can move on to the real issue...

2) I would like Master spreadsheet to have a button that scans Client Data's column B (CODE) for matches to it's own column B (CODE). If a match is found copy Client data C to Master's C and overwirte Master's A with Client's Data A. (This will ensure names are also spelled properly in the master.)

I believe this is the easiest way to have birth dates populated in the master by code instead using the names in Master to do it (which have been know to be misspelled incorrectly. Codes are very rarely mispelled)

Whew... John, if this is too much to ask I understand... Or maybe its the kind of challenge you like. Hopefully thats the case..

Regards, Ken

Hi Ken

Let me look at it. What you've asked here

compare each row in column A against each in column D and if a match is found move corresponding match E (birth date) to corresponding match C (blank).

is not terribly difficult (said the blind man).

I'll need to think on this

I would like Master spreadsheet to have a button that scans Client Data's column B (CODE) for matches to it's own column B (CODE). If a match is found copy Client data C to Master's C and overwrite Master's A with Client's Data A.

and how this will be integrated into the process. I don't have Client Data in the files I'm working with to properly test this part.

I'll get back to you with the first part probably tomorrow.

Hi Ken
Ya well the first part was relatively straightforward

Please Login or Register  to view this content.

Now that we have this need to figure out how to apply it. I'll think on it.

Hi Ken

I've thought of how to implement the Client DOB update and am thinking about this approach:

1. Write a procedure in Client List to do a one time update to Master to fill in missing DOB and correct name spelling.
2. Incorporate code in existing Worker 2 (where DOB lives) code to fill in missing DOB in Worker 2 then process to Master.

I'd appreciate if you'd tell me what you think the process should be based on your environment.

[*]Write a procedure in Client List to do a one time update to Master to fill in missing DOB and correct name spelling.

By one time you mean on command, right? Im guessing this procedure will occur once ot twice a month because it requires querying our internal software manually and pasting it in to this special workbook.

[*]Incorporate code in existing Worker 2 (where DOB lives) code to fill in missing DOB in Worker 2 then process to Master.

Hmm... Well it would remove the need for another workbook to be placed in the folder but... Worker 2 isnt going to be the person who will run updates of master from the cleint list , the manager working with the master is. Im thinking it deserves its own file. Besides, wouldn't the only thing needing to be done now is making a macro that goes in the master that can check clients with blank DOB's against column C in the Client List v1? (And mispelled names)
Ken

Hi Ken
Attached are 5 files; all 5 files should live in the same folder. The Client List procedure can be run at ant time (on demand). At the moment the code does not close the Client List book or the Master book. If you wish it to do so uncomment these lines of code in the Compare procedure

Please Login or Register  to view this content.

The Worker 2 workbook has code to update DOB as part of the Worker 2 save/close procedure but it's been commented out. If you want to play with it uncomment this line of code in RunMe procedure

Please Login or Register  to view this content.

Let me know of issues.

Attached are 5 files; all 5 files should live in the same folder.

John,

I just added the Client List workbook to the exhisting folder and it works great... Unless im missing something, I dont think I need to replace the other files.

Also, since the button is on the client list workbook does that mean the macro is testing the list of names in that file against the ones in the master? I was thinking that since the list of names in the master is faaar shorter it would be prefferred to test them against the client name workbook. (not that it matters because it works fine)

Ken

Hi Ken

Regarding this

the button is on the client list workbook does that mean the macro is testing the list of names in that file against the ones in the master?

The Button runs two procedures. First it updates the Client List Column C with DOB from Client List Column E. That's the Compare procedure. After that's done the Compare procedure then calls the UpdateMaster procedure. It opens the Master file, takes the client code in the Master file and looks for it in the Client List file. If it finds it in the Client List, the DOB and Name from the Client List file is copied to the Master file.

So, no

since the button is on the client list workbook does that mean the macro is testing the list of names in that file against the ones in the master?

It's doing this

since the list of names in the master is faaar shorter it would be prefferred to test them against the client name workbook

Regarding this

Unless im missing something, I dont think I need to replace the other files.

I did make some code changes to the code in Worker workbooks. Most were minor changes. The major change was made in Worker2 workbook. I added code such that, before Worker2 data is copied to the Master file, Worker2 code opens Client File and looks for DOB and adds it to Worker2 file. Same process as described above except that we're comparing Worker2 file to Client File. In doing this, if DOB is missing in Worker2 file it gets filled from Client File. Then Worker2 data is copied to Master file WITH DOB so that DOB is not missing in Master file...it's filled up front instead of after the fact.

As I indicated previously

The Worker 2 workbook has code to update DOB as part of the Worker 2 save/close procedure but it's been commented out. If you want to play with it uncomment this line of code in RunMe procedure

Please Login or Register  to view this content.

Hope all this makes sense...if not let me know...we can make it do what you wish.

Regarding this I did make some code changes to the code in Worker workbooks. Most were minor changes. The major change was made in Worker2 workbook. I added code such that, before Worker2 data is copied to the Master file, Worker2 code opens Client File and looks for DOB and adds it to Worker2 file.

I can update the file.

Since this file is now reading the client list could we add a few lines of code so its updating more than just DOB in Worker 2? There are a years worth of entries on other sheets in that workbook that do not have CL codes entered. (She just started entering them in) I have not copied your code from Sheet 1 to the others because of this. If Worker 2's book had its names searched against the names in column A of Client List it could take column B's CODES and fill in all of the column B's throughout the workbook. (I have already added the CODE column on the other sheets within so they are indetical to sheet 1...

I know this means we would be checking names that arent necessarily spelled correctly with perfect names but i'm guessing 99% are correct and thus take out 99% of the work to eventually get a years wirht of info in to the master. (note, the names are not always all caps but should be spelled the same and have the same comma and space between last and first name...

Hi Ken
Not exactly sure what you're asking. Are you suggesting that you have existing data in Worker(x,y,z) worksheets and that you'd like to add Worker x and Worker y and Worker z (etc) Client Codes to these worksheets from the Client List file based on NAME?

If I'm understanding correctly...sure...this can be done. You need to define what it is you'd like to do...just sitting here sipping on my Bourbon Supreme, I'm thinking "this would be a one time update to the Worker(x) worksheets"

It would be a lot more convenient were we sitting in front of your computer screen and you were SHOWING me what you have and WHAT you'd like it to be...alas...we don't have that. So, explain as best you can...show me best you can. We'll make it happen best we can...if we can.

This all would need to be tested (on your end) on copies of your "live" files to make certain we get the expected results.

So...if you would...detail the process as you see it. In the meantime I'll noodle it. Get back to me.

Not exactly sure what you're asking. Are you suggesting that you have existing data in Worker(x,y,z) worksheets and that you'd like to add Worker x and Worker y and Worker z (etc) Client Codes to these worksheets from the Client List file

Exactly... On Worker 2 only, there are 8 other sheets (January-September) that have 100's of client names and data that would populate the master list IF they had codes referenced to them. (They do not because the worker was never told to put them in until we started this little project.

I know that if I copied your VB code from one of the sheets in to another it would attempt to update the master but alas, these previous sheet have no codes. (They dook like exacltly the same however, I added a code column in the same place. It would in fact be a one time search of the client list but it would likely add 1000+ codes to past clients and thus make the master twice as rich in information. Also, those clients that are added in to the master are ideal to add as they will cross grace the desks of the other workers in time and so the table will be beutifully filled in and we can run some very power pivot tables.

just sitting here sipping on my Bourbon Supreme,

Nice, I can only do bourbons and some whiskeys... No clear alcohol for me anymore. Doesn't sit right.

Hi Ken

I'll get back to you later today. Should be able to do this.

Ken Ill be 70 years old in less than two weeks...I do just about anything I please (as long as this old body and still nimble mind can handle it )

Hi Ken

I've added another procedure and button to Client List to update Worker 2 file.

The procedure works like this:
For each name in Column A in Worker 2 worksheets if Column B "Code" is blank it looks for that name in Client List Column A. If it gets a hit the Client Code is copied to Worker 2 worksheet Column B.

The procedure will close Worker 2 workbook and save it but Worker 2 Code WILL NOT RUN. This is done intentionally. Were you to manually close Worker 2 the code WILL RUN. I've left Client List open...you can have the procedure close it by uncommenting this line in Module 3

Please Login or Register  to view this content.

Let me know of issues.

Hi Ken

In Module 1 (RunMe) of Worker 2 workbook comment out this line of code (it's the last line of code before "End Sub"

Please Login or Register  to view this content.

Now, the only problem with that is, when the User MANUALLY closes Worker 2 workbook the code will fire again. This PROBABLY won't be an issue since the processed records have been marked with an X...but watch it carefully. Don't know at present of a way to prevent this.

Let me know of issues...'gotta go to a meeting.

Hi Ken

You're welcome...glad I could help.