Happy Friday MS Office wizards.

Got a little project to do and though Ive done it before with a lot of data hacking and cleansing, I suspect there a better and more perpetual way of doing it, particularly so I can continuously add new months of data to the respective sets without a complete rebuild. A sort of template layout

Basically I have two different data sources which produce very different data quantity and layout but which i can easily paste into Worksheet 1 and worksheet 2 as per the example picture.

But there is a unique identifier fields common in each, the Name being the key one one and other specific data like illustrated item number date qty etc. that are the ones I need to analyze.

So in the example I want to be able to provide summary data, by customer of their shipments and retuns in any given month

having written this post it seems quite simple! But this is quite big data.

To add I'm reasonable with Excel as long as its using internal functions, (i cant write macros or anything fancy) but I am incompetent at Access!, so that not a road Id prefer, nor for the target audience.

Grateful as ever for your thoughts guys.......


book1

[Edited on 23/10/15 by ReMan]

I know more about access, so I won't be much help. In access it would be a simple matter of creating a query (if I understand your post correctly) where the monthly data could be added or edited to update the main table. In excel is it not possible to create separate work sheet (like access query) that will update the main table. Failing that maybe a micro is the answer. BTW the data looks more suited to a database than a spreadsheet, but if it works for you...

Haven't looked at this properly but won't '=Sumif' function help you.