Best way to bring in data from one excel sheet to another?

My Dept has a survey that they send out to members once a year. The data gets submitted to us via an excel spreadsheet/template.

What we'd like to do is be able to offer those that have participated in the previous year's survey the ability to have access to their information they submitted from the prior year. We have about 300 participants in this survey, and the survey could take 2-3 days to finish it from scratch. Each participant has a survey ID so tracking the data should be relatively easy.

Preferably we’d like to import their past year data into the current year’s template; linking their data to the appropriate cells.  We know we can do this manually but we receive 300 responses each year and we need to find a way to streamline this process, without having to open each individual excel file.

I'm not an excel guru by any means, but have been asked to help to see if I can aid in making this easier as I'm the sys admin for the department. What is the best route for me to go here?
nflynn85Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Darrell PorterEnterprise Business Process ArchitectCommented:
Can you attach a copy of the base survey, possibly with sample/example data?
That would help us make the answer more complete for you.
Roy CoxGroup Finance ManagerCommented:
Depending on the data layout and the template you could probably use VLOOKUP or INDEX/MATCH. A workbook with some sample data and the template form would help provide a solution
nflynn85Author Commented:
Attached is the template/sheet that will be used on an annual basis going forward
test-sheet.xlsx
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Roy CoxGroup Finance ManagerCommented:
I can't see what you want to import or where to
nflynn85Author Commented:
So I copied the sheet into another xlsx file since it's static and not going to be changing from year to year

In the previous year file, I included a line of sample data (all data types are included at the top of the sheet). I could not provide the original, for security reasons. Also note, anything that does not have the 'number' data type at the top is chosen from a drop down menu (not sure if that'll effect anything or not)

I hope this paints a clearer picture. Let me know if you need anything else
current-year.xlsx
previous-year.xlsx
regmigrantCommented:
your requirement isn't clear to me - have I got this correct:-

The respondents will each fill one or more lines of the 'current year' sheet and send it back to you
The response will be tracked by a reference of some sort identifying who returned a completed sheet
As part of sending out the sheet to be filled you would like to include their answers from last years sheet

So you want a way to read all the previous years sheets (approx. 300) and show them the data they previously filled? this is not difficult in theory but why not just send them a copy of their previous year sheet as well as the new request?
nflynn85Author Commented:
That's correct. We're just trying to streamline this process so that the amount of work is minimized on both ends. I just wasn't sure if there was a better way to do it than a copy/paste operation or if just providing them the survey from last year was the way to go.

Ideally the less work it appears the customer has to do the better in the end
regmigrantCommented:
OK, so working with a  copy of all last years responses in a chosen folder you need a routine that renames the tab to 'previous responses' and puts a new tab for this years response at the front and then saves it - ready for distribution. It should also delete any existing 'previous year' tab (ie: two year old data) if it exists  then you could just copy/execute/re-issue year on year.

The alternative is that they get two files and can look up their old response if they want to open the second one (which doesn't seem too onerous but could lead to them filling the wrong one in and being a tad annoyed).

Is that approach acceptable?

One other thought is that they may just copy and paste last years data - is that a potential issue?

Its near the end of day here so I may not be able to respond for a while
nflynn85Author Commented:
Yeah I really like where you were going with that initial thought in your above reply. Is there any way to automate that process? Doing all of that manually (300 times) would be a huge black hole of time.

The alternative you mention (as well as the copy paste), were methods that I had previously mentioned to my team members.

I don't think any approach is or is not acceptable at this point - we just want all of our options in front of us so we can make the best decision going forward
regmigrantCommented:
the code to do that would be relatively straight forward but I didn't want to start something if it doesn't match needs. There would be a couple of limitations to consider:
- is it ok to lock down the workbook structure to prevent modification of the tab names
- what to do if we find a sheet with incorrectly named tabs
- how does it fit into existing process

assuming you can work around these I'll put together an initial attempt
nflynn85Author Commented:
The final workbook will be completely locked down. We will not be allowing customers to modify the workbook structure. My team made this oversight last year, and are implementing that change this year.

There shouldn't be any tabs named incorrectly, we can instruct customers on how to properly submit the data and we can always do a quick double check on our end before running the code.

Can definitely work around all of that :)
regmigrantCommented:
here's a first attempt, I'm assuming a certain level of VBA support- ie: someone can open the VBA editor and modify the constants I put at the top; if that's not possible then I can add in some dialogs but it would be good to confirm this is going in the right direction.

I've not done extensive testing but the basic flow is in place.

Copy some of the previous year surveys to a folder and create a new empty folder to receive the changed files.

Put the folder names into the Macro - there are Constants defined in the first few lines- then run the macro

It should run through all the files, add the new survey request, rename the old Tab to 'Previous' and save it in the new folder - the originals should not be updated

However I've had to make assumptions about file and tab names

 - I've tried to show where in the comments so you can adjust as needed but get back to me if you don't have vba support

- there is no check for duplicate files in the target directory (so running it a second time will fail unless you empty it)

- there are links in the control sheet which won't resolve on my pc, i can suppress them if required but assumed they might be relevant to what you are doing and they will update on your machine. For testing purposes you can just update or not as needed if the dialog pops up
nflynn-iteration-1.xlsm
nflynn85Author Commented:
WOW thank you for that! I can somewhat follow along with what you have here. It's been a long time since I've touched VBA code (college...10 or so years)

I will make some edits and see if I can get things working
regmigrantCommented:
here's a second iteration  - a bit more robust on the error handling and tested with a few more files.

Setup is the same in terms of previous/current folders and files but you can now quote the folder names, current year and  what the sheets are actually called on the control sheet. - I've highlighted the boxes to fill in so you don't need to edit the vba

So, for example, if your previous surveys all use 'Sheet1', you can put that in the 'Previous sheet name' and choose what you want to call this years tab in 'Current Sheet name' - it doesn't check that the tabnames are valid and the routine will fail if you put a bad one in, I can add that if you want

You will have to give me a steer on how to handle the file names, at the moment its just appending  the current year on the end of the previous file so if you re-use next year it will be survey346 2015 2016 and so on
nflynn-iteration-2.xlsm
nflynn85Author Commented:
Nice touch with just having to update the control to get the updates in the VBA!

Is there any way to have this set up so that when it runs, it isn't dependent on what the previous tab name is or was? I think it's great how it spits out a new file and gives you current and previous year's data. Exactly what i'm looking for.

As far as the file name is concerned, I think it will become cumbersome if we have to keep modifying it like that (I feel like we'd have to manually go in there and remove it, which we don't want). Is there a way to strip the previous years stamp/date, and add the current year's date?

This great, thank you so much
regmigrantCommented:
Those are the kind of problems that look simple but will need a bit of discussion:-

As it's written at the moment it can be run year on year and will keep moving current to previous but has the issues you point out, these are a result of trying to convert the current format to a new format whilst being usable for subsequent years (so we have to deal with both formats in the same routine):
- it needs to know which of the two tabs is the 'old' previous and which is the 'last year' entry and to do that it uses the sheet names. If we don't have clarity on those then it can't decide which to remove
- the filename obviously needs work but we have to get over the fact that the current filenames don't match what we are moving to so the routine has to deal with today's format AND the format that will exist next year.

I see two possible ways forward:-
We use a separate routine to reformat the current files - changing the sheet name (to a name of your choice) and reformat the filename (to a format of your choice) and then this routine is starting from a known baseline. It means creating yet another version of the files and you'll have to manage the transition through any audit (assuming you have them).
NB: the filename in the sample is obviously an example so I would need confirmed filename formats (what it is and what you would like it to be) and what Tab Name I should change it to


Alternatively we leave the filenames as they are and use dates in the tab names:-
I'll change the routine so that if it finds only 1 sheet it changes that sheet to be Response2014 (for example) and adds in the new one as (for example) Request2015.
If it finds two sheets it will delete the one with the oldest year and rename the newest one from Request to Response and so on.

This will reduce the number of full copies and keep the filenames consistent for audit purposes but the drawback is that you will need to open the file to see if its a new or old format (which is why I added the year).

Let me know which way you would like to go
nflynn85Author Commented:
I definitely like the second route the best - I definitely like the idea of not having to edit the file name on a year to year basis but doing it internally to the sheet would seem like the most logical way to handle it
regmigrantCommented:
Hi again
I've made the changes discussed in a further iteration - (actually two iterations since the first one had some schoolboy errors)
Folder setup is the same
yellow highlight shows the variables
put the target year in and previous year is 'calculated' - you can overwrite that if needed
you can choose what you want the tabs called in E - this text will be used to name the new/previous tabs and the year added

Not had a chance to properly error check but have added an alert if it finds a tab that doesn't match what's expected. This will trap any existing 'previous' workbooks that have more than one tab and a year isn't present in the name (ie: it has more than one tab and the extra was not added by this routine). if there are a lot of those we will have to look at an alternate way to manage them

This should be fairly close to finished (assuming testing goes well) and could stand some tidying up of the entry sheet - but you can do that yourself as long as you remember to change the VBA ranges of the input variables should you move the cells.
nflynn-iteration-4.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nflynn85Author Commented:
This is incredible. Thanks so much for your help with this! Great Job!

I did some testing and it is working as expected. Simple and to the point. Works like a champ.
regmigrantCommented:
Glad it does what you need, I was tidying some stuff up so might as well post the final version :)
nflynn-iteration-5.xlsm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.