Combining Data of varying lengths

[jaj8372]

Afternoon all,

First of all, thanks for reading this. I am not entirely sure I have even managed to describe what I want to do in the subject title properly but, using an example, I will try and explain what I need to do and how I am failing!

THE FIRST PART - Basically, for this example I have three spreadsheets in three separate files/woorkbooks source1.xls
source2.xls and
destination.xls

In source1 & source2 I will have a series of rows of data for example someone's name, address, phone number etc etc with a different person on each line. (Source1 and Source2 are pretty much the same type of file with similar information but are just stored in different locations and edited by different people.)

What I need to do is create a master list of all the combined data in sources1 & 2 and produce it in destination.xls. That seemed simple enough initially but now seems a bit more complicated!

THE SECOND PART of this makes this whole thing more confusing - what I need to do after this is be able to dynamically add new lines into the destination.xls file as and when they are added to each of the source spreadsheets for example, source1 might contain details of 20 people which (hopefully) would be replicated on destination.xls, but as soon as a user adds data to the 21st line it too would be added to the destination and nestle in amongst all the other data...

and the icing on the cake (which technically is a separate project that I could do with filters!) ... would be to only copy over the name once if it is a duplicate!

Please help me with this as I am going mental trying to work out the best way to sort this out!

Thanks in advance...

[JBeaucaire]

I would think a workbook_open macro in the Destination workbook would be the way to go. Each time you open the workbook it essentially secretly opens Source1 and Source2, copies all the data into Destination, closes the source workbooks, sorts the new data and deletes any duplicates.

If you want to provide a usable set of sample files (including duplicated info needing to be spotted and removed), as well as the full path to where the source workbooks will be stored, I can write something up for you.

I do not recommend you try to make this some sort of "realtime" thing. Just something that happens each time you open the destination workbook.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbooks.

[jaj8372]

Thanks for the speedy reply...

I was hoping that there might be a way to do this without macros as I'm not sure a) how in hell to do them and b) if they'll work on the intended computers as they may not be able to enable them... but lets give it a shot anyway!

I have attached a zip with three generic (very unimaginative and made-up) spreadsheets, the two source docs and the destination one. Basically I need the data from the two to appear in the destination one - but the most important thing being that when someone adds to either of the source docs which will be saved onto a different part of a network, the destination will automatically populate with the original and new info.

As I just mentioned, the source sheets (which could number up to ten when deployed in reality) will be located all over a shared network but suffice to say a generic location of x:/projects/OurProject/Myproject would be the sort of structure.

Thanks once again for any help and sorry I forgot to mention that my example sheets are in 2007 format but I'm fairly sure I only have 2003 at work!

[JBeaucaire]

Ok, so there won't be any duplicate values between the two source books. Good to know. Maybe there's a non-macro way, I'll think about it a bit.

[JBeaucaire]

Use this Destination file, it has remote links on the two sheets Data1 and Data2 to bring over the contents in the Source1 and Source2 files. You'll need to use EDIT > LINKS to change the source to where your files are stored on your system.

The two data sheets will be a reflection of the data in the source workbooks, don't worry about any errors you see in columns A:E as they won't matter. Columns G and H on those sheets create a key and an index used to draw back the unique values from those two pages to the Master sheet.

[jaj8372]

Wow,

that was some seriously fast solving! Thank you!

I have had a quick dabble and all looks ok - but I'll have to try it in anger at work tomorrow...

Thanks once again for such a speedy reply!!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[jaj8372]

Back again!

I have another query related to this exact problem and solution you so kindly gave me....

I am now trying to incorporate more source documents to be 'read' by the destination spreadsheet however, as was kind of to be expected, is isn't as simple as copying the last bit of code and duplicating it changing the reference to the third source sheet - this makes the argument appear to have too many sections...

Please help me! I have only just managed to get my head round this nightmare!!

[JBeaucaire]

Ok, I'm listening.

[jaj8372]

I have been going over the argument (if) statements and have realised that if I need to expand the functionality from two spreadsheets as above to include drawing data from in excess of 40 source spreadsheets then I think I might need to totally re-think the methodology of obtaining the data - maybe using macros?
My knowledge and ability of excel statements is limited but my knowledge of macros is non-existent. Would this be a suitable way to do it do you think?
And if yes... using the basic layout of the above examples how would I go about it?

[JBeaucaire]

Let's start completely over.

Provide 2-3 sample "source" workbooks.
Provide a new destination workbook showing how the data from 3 sample workbooks is drawn in and displayed.

Will the source workbooks be in a specific folder all the time, or does the macro need to prompt you to "pick a folder" each time? (That could get irritating if the folder is the same all the time)

I can help construct a quick macro to do it, but you have to give as much demonstrative detail as you can to make it easy to do.

[jaj8372]

Hi there,

As requested, I have added some demonstration sheets.

Basically, the same requirements exist in that there will be multiple source sheets from which to get the data (which once they are created will remain in their respective project folders all over a designated drive). The data consisting of personal details will probably differ in number of rows per sheet i.e. one source spreadsheet will have 5 rows another 6, another only 4 etc.

As before, I need the 'destination' spreadsheet to show the content of the individual source spreadsheets (which currently number up to 40!) without opening the sheets themselves if possible.

The solution you had above worked perfectly for two sheets and I was able to tweak it for exactly what I needed it but unfortunately it looks as though you can't add any more than two sheets without it getting mega-complicated?

I hope this helps to explain a bit better.... and any help is extremely greatly appreciated!

[jaj8372]

Just to add for clarity - once the source spreadsheets have been created in an individual project's set of folders it will not move again. There will however be the need to add further project's source spreadsheets at a later date (at least one a week) as and when needed - but this need not be done by the end user - in fact I'd rather they didn't - I'd prefer to add it in the code or something...

Thanks

[JBeaucaire]

At this point, using a macro will make this the simplest, even if you're not familiar with macros, this should be easy enough. Here's the code I wrote for you:

Please Login or Register  to view this content.

I've installed it into the sample workbook attached. Look on the sheet FILES and you will find a path/file listing to where I tested this on my system. Edit that so it points you your files on your system. Make sure the paths are exactly correct.

You can add as many files to the column A listing you want, in as many random locations as you wish. The macro is going to go through them all in the order they are listed, if that matters.

Then switch back the the MASTER sheet and press the button, the rest is automatic. Even with a lot of filenames, this should be speedy enough.

=============
You can install it into your other workbooks by:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list. (Or you can add it to a FORMS button on your worksheet, like I did)

[jaj8372]

Wow... Thank you so much - I really do appreciate it!

This is so helpful - thanks for all your effort