- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Help - Suggestions
Posted on 4/19/24 at 7:44 pm
Posted on 4/19/24 at 7:44 pm
I have a weekly report that I get in excel that has seperate tabs by department that shows utilization. I'm trying to get a yearly summary of these reports. I can't get it from the source because each excel typically has to modified manually for accuracy. It's 52 documents with 7 tabs each. The columns always remain the same but the rows change with the flucations of people.
What is the best way to combine these? A summary of each department would be sufficient, but individual personnel summaries would be nice. Right now I'm thinking of combining each department into an excel and doing some vlookups but that seems tedious. Anything I'm missing here?
What is the best way to combine these? A summary of each department would be sufficient, but individual personnel summaries would be nice. Right now I'm thinking of combining each department into an excel and doing some vlookups but that seems tedious. Anything I'm missing here?
10
Posted on 4/20/24 at 5:25 am to Piece
Honestly, Excel wouldn't be the right tool to do this, in my opinion. VLOOKUPs would be tedious and the sort has to be correct, or the formulas will provide the wrong result. You could import each spreadsheet/tab into Access and query any way you want.
You'd need to add a couple of columns to each spreadsheet to show which week and tab for tracking, but each week you could import and append the data to a master list. Then you've got all the records in one place to report on as you go along.
Just some thoughts. Once you get it set up, it's fairly straight forward.
You'd need to add a couple of columns to each spreadsheet to show which week and tab for tracking, but each week you could import and append the data to a master list. Then you've got all the records in one place to report on as you go along.
Just some thoughts. Once you get it set up, it's fairly straight forward.
Posted on 4/20/24 at 5:46 pm to Piece
I'm not very experienced in using it, but Power Query Editor allows you to import data from all files within a folder (as long as they all follow the same format).
Posted on 4/20/24 at 7:42 pm to Piece
What you need is called a “pivot table”. Just have whoever produces the report for you learn how to do it.
Posted on 4/20/24 at 8:36 pm to Piece
copy the 7 tabs into one tab(assuming the columns are the same on each tab) and make a pivot table.
This post was edited on 4/20/24 at 8:37 pm
Posted on 4/21/24 at 10:48 am to Piece
You could probably do some powerful work with indirect and sumifs or xlookup.
Use indirect to find the tab you’re looking for and sumifs or xlookup to find the data you’re looking for.
Use indirect to find the tab you’re looking for and sumifs or xlookup to find the data you’re looking for.
Posted on 4/22/24 at 9:58 am to Piece
I would use power query in two steps.
One to combine all the tabs into one data source.
Then query all 52 documents into one master file.
Then you can slice and dice like you would any table in excel.
One to combine all the tabs into one data source.
Then query all 52 documents into one master file.
Then you can slice and dice like you would any table in excel.
Posted on 4/24/24 at 6:10 pm to Piece
Don't force the current tool to do what you need. Think outside the box. That data is coming to you in excel format from a database. They are using something such as SSIS or SSRS to create that. Discuss with the source of that data. Tell them your business need and how much time you waste formatting it how you need.
Let them create the right solution a d you both celebrate the value add opportunity and cost savings.
Let them create the right solution a d you both celebrate the value add opportunity and cost savings.
Posted on 4/25/24 at 10:38 pm to Piece
The only non tedius way to accomplish this would be to write a VBA program to open each workbook/tab and copy the data from each tab into a consolidation table. You would append each tabs data onto the end of the consolidation table.
This would get all of the data into one sheet which you could then slice/dice via pivot table to provide your analysis.
Depending on how the data is structured, you might have to add a column or two to add date time, division, etc... This would be easy to do in the VBA script.
I'm currently solving a similar problem at work by this method, the only difference is I'm doing it Google Sheets with Google Application Script instead of Excel with VBA. Its the same process and result in both apps.
This would get all of the data into one sheet which you could then slice/dice via pivot table to provide your analysis.
Depending on how the data is structured, you might have to add a column or two to add date time, division, etc... This would be easy to do in the VBA script.
I'm currently solving a similar problem at work by this method, the only difference is I'm doing it Google Sheets with Google Application Script instead of Excel with VBA. Its the same process and result in both apps.
Posted on 5/3/24 at 11:51 am to Piece
Have you gotten the solution you wanted for this? I’d suggest PowerBi. I’d be willing to help
Posted on 5/3/24 at 12:46 pm to Piece
Gonna echo what others have suggested and recommend giving Power Query a look. It is not a substitute for a true ETL tool but it is still powerful and flexible, and integrates seamlessly into Excel and Power BI. The amount of random junk you can use as a data source alone is incredible.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News