How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy, Software Engineer
Category: Programming
Satisfied Customers: 1965
Experience:  Extensive Knowledge in PHP, MYSQL, CSS & Javascript
Type Your Programming Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I have dozens of excel spreadsheets to consolidate and

Customer Question

I have dozens of excel spreadsheets to consolidate and reference. My goal is to compare attendance figures and weather, to plan for future attendance numbers based on past results depending on the time of day, temperature, and weather. I've been manually copying data using transpose and "transform range" from Kutools, to get one single column and pasting to a master spreadsheet, but it's been taking forever. If you have another strategy I'll love you forever.
Here's an example of the two basic spreadsheets here: . I have 5 years of summer months to do to establish a good weather variance, if it matters how many sheets.
Submitted: 10 months ago.
Category: Programming
Expert:  The-PC-Guy replied 10 months ago.

Hello, I am The-PC-Guy. I will do my best to try and help you.

what exactly is it you are trying to accomplish What are you trying to copy, from where to where?

Customer: replied 10 months ago.
thanks for the response. So my ultimate goal is to do an attendance forecast. I'll take what the previous attendance numbers have been hourly, combined with the typical weather association, and average what the attendance will be considering the already established patterns and the temperature estimated for each day for next year. For example, if June 1st is predicted to be 75 degrees next year and no rain, based on previous attendance numbers at that time since 2012, the attendance will be 5 people at noon, 10 people at 1, etc. I hope that explains it. I can always share the data from my google drive if it helps. It's standard local government stuff, so it's not confidential or anything.One of the first big problems, before I tackle how I'd get the attendance numbers, is how to bring all the data together into a master sheet to compare it all, view graphs, etc. Maybe I don't have to bring it all together to compare it and I can just reference each spreadsheet, but as you can see from the imgur link, the tables are in different formats, and have a significant amount of unneeded information.Hope that helps.
Expert:  The-PC-Guy replied 10 months ago.

there in lies the problem with combining different data sets.

Is there any common field between the 2 sets, anything at all?

I can certain help you with importing from multiple sheets to a single master sheet. But if you want to combine the data there needs to be at least 1 common field between the 2 sets of data.

As for the analysis, that is not my area of experties, I know there are a bunch of plugins for excel that can do various statistical analysis and forcasting, simulations and scenarios. I can reccomend a few, but as far as how to use them would be up to you to figure out.

Customer: replied 10 months ago.
the dates and times of the data would be the same fields throughout all the data. the big problems would be how it is listed and the differing format depending on which data is referenced. The attendance sheets are separated by time on rows and dates on columns and grouped in each time by the different specific attendance categories. The sheets listing weather conditions are the same basic format. The sheets listing the specific weather are listed by date in the first row and time in the next, and all data like temperature, wind, etc. on the columns.
Expert:  The-PC-Guy replied 10 months ago.

if you could send some example excel files that might help.

Customer: replied 10 months ago.
Expert:  The-PC-Guy replied 10 months ago.

well I certainly can create a macro that could combine the 2 sets given that they both have dates and times, it is just a matter of merging the info in that circumstance.

Also you said multiple sheets, does this mean they are in multiple workbooks?

Customer: replied 10 months ago.
there's data from 2012 to 2016, late may to early september. There's a workbook for each year with the weather description, attendance, and temperature all separated, and worksheets for each month. Some information is missing too here and there, if it matters.
Customer: replied 10 months ago.
are you able to make the macro?
Expert:  The-PC-Guy replied 10 months ago.

assuming that all the sheets are in exactly the same format and I mean exactly, it shouldn't be a problem to automate it. I don't think I would make it look exactly like your master, I have a different idea in mind that should make it easier.

Now for the cost.

This is a very complex issue and will cost at least $300. This is just for the population of a master sheet, obviously anything additional would add to the cost.

I'm sending an addtional service offer, if you accept it I can get started.

Customer: replied 10 months ago.
Thanks, ***** ***** it wouldn't be a simple answer. Unfortunately I can't afford that, so I'll just go back to my original strategy of copying and pasting.