Need help wrangling spreadsheets
January 12, 2016 1:23 PM Subscribe
Spreadsheets are making me crazy.
Is there anywhere I can go or anyone I can call to show me how to do very specific things in Excel? I have a ton of spreadsheets, each with partial information. I need to find an easy way to combine them together so I have all the info in one place and then have them do very simple things that I know excel is capable of, such as showing time elapsed since a certain date, or pivot tables within pivot tables, if that's possible.
Right now I am literally spending dozens of hours mashing together different spreadsheets in an INCREDIBLY tortured fashion, but it's taking SO many steps that it's all highly prone to error. Plus I'm wasting an ungodly amount of time. I can't help but think that this has to be easier. I could go take an excel course, but I already know how to do most things in excel (except macros) so I feel like I would learn a bunch of things I already know, maybe a few new things, but still not know what I need to do for my specific issue. I'd rather not do that. If I could find a service or helpline that I could show all my spreadsheets to, tell them what I want the end result to be and have them show me how to do it, that would be ideal.
I've read online help sites, I've watched tutorials, and I haven't found anything. I need customized advice.
I don't think my problem is overcomplicated, it just requires a lot of wrangling that I don't know how to do. Please tell me if something to help me exists, thanks.
Is there anywhere I can go or anyone I can call to show me how to do very specific things in Excel? I have a ton of spreadsheets, each with partial information. I need to find an easy way to combine them together so I have all the info in one place and then have them do very simple things that I know excel is capable of, such as showing time elapsed since a certain date, or pivot tables within pivot tables, if that's possible.
Right now I am literally spending dozens of hours mashing together different spreadsheets in an INCREDIBLY tortured fashion, but it's taking SO many steps that it's all highly prone to error. Plus I'm wasting an ungodly amount of time. I can't help but think that this has to be easier. I could go take an excel course, but I already know how to do most things in excel (except macros) so I feel like I would learn a bunch of things I already know, maybe a few new things, but still not know what I need to do for my specific issue. I'd rather not do that. If I could find a service or helpline that I could show all my spreadsheets to, tell them what I want the end result to be and have them show me how to do it, that would be ideal.
I've read online help sites, I've watched tutorials, and I haven't found anything. I need customized advice.
I don't think my problem is overcomplicated, it just requires a lot of wrangling that I don't know how to do. Please tell me if something to help me exists, thanks.
Fiverr?
posted by Foci for Analysis at 1:30 PM on January 12, 2016
posted by Foci for Analysis at 1:30 PM on January 12, 2016
You may want to take an advanced Excel course that would teach you about macros and VBA, which will allow you to write scripts to perform complex tasks. You can, of course, hire a consultant to take care of this, but if you want to learn to do it yourself, macros and VBA are what you probably need to learn.
If you look on Upwork, you can find Excel experts in India and other places that won't cost you as much per hour as hiring someone locally, like $10-12/hr vs $35-40.
posted by ananci at 1:32 PM on January 12, 2016
If you look on Upwork, you can find Excel experts in India and other places that won't cost you as much per hour as hiring someone locally, like $10-12/hr vs $35-40.
posted by ananci at 1:32 PM on January 12, 2016
Best answer: What you want to start with is PowerPivot. It's an add-in that's free from Microsoft. It's absolutely BOSS!
It allows really great interaction with multiple spreadsheets, and you can either renew it through a separate spreadsheet or Access, or even some other software.
There are some great, comprehensive videos on You Tube.
Also, check out Slicers. These motherfuckers changed my life.
posted by Ruthless Bunny at 1:37 PM on January 12, 2016 [2 favorites]
It allows really great interaction with multiple spreadsheets, and you can either renew it through a separate spreadsheet or Access, or even some other software.
There are some great, comprehensive videos on You Tube.
Also, check out Slicers. These motherfuckers changed my life.
posted by Ruthless Bunny at 1:37 PM on January 12, 2016 [2 favorites]
Would vlookup be helpful? If you are trying to combine information from multiple sheets and they have a unique value in common that could be used as a lookup, it might make getting all the data on one sheet much easier. If you need to combine multiple vlookups (the data could be in sheet a or b), read up on iferror, it will let you nest vlookups.
posted by Apoch at 1:57 PM on January 12, 2016 [2 favorites]
posted by Apoch at 1:57 PM on January 12, 2016 [2 favorites]
Look to your local providers of the courses without the courses themselves in mind. If they are like the ones I've used before, they will also offer a 'fix my software puzzle' service plus the individual instructors are often freelancers who work with those programs around times they teaching the courses.
posted by Trivia Newton John at 2:59 PM on January 12, 2016
posted by Trivia Newton John at 2:59 PM on January 12, 2016
PowerQuery, like PowerPivot, is made for this and free. It can create a little relational database with just excel, referencing tables on different documents or data sources.
posted by munchingzombie at 3:20 PM on January 12, 2016
posted by munchingzombie at 3:20 PM on January 12, 2016
Response by poster: Thanks everyone. I haven't downloaded any add-ons because I'm not sure if that's cool to do on my work computer, though I suppose I could ask IT tomorrow.
I briefly tried Access but realized I didn't really know what I was doing so stopped (but I'm open to trying again if that's the best option).
A brief (and simplified) description of what I'm trying to do: say I have four spreadsheets. The common info on all spreadsheets is account name and number. The spreadsheets are accounts that have been flagged with various alerts.
Spreadsheet #1 has all accounts listed (master list)
Spreadsheet #2 has all accounts with a single specific violation. Additional info: violation name and a quantity. There might be multiple items (lines of data) that trigger this violation, so I need each line rather than one general flag on the account.
Spreadsheet #3 has accounts falling outside a certain variance. Additional info: each account's specific variance target thresholds and the amount outside the variance
Spreadsheet #4 has all accounts with something unverified. Additional info: what is unverified and an actual degree of something
Not every account will have a violation, and some accounts will have one or more violations.
I want to have one spreadsheet that is sorted by account which has all violations (if any) by that specific account grouped together. The violations can either be on rows or in columns. The problem I'm running into is that each alert has different info contained within, which is why I was trying to figure out how to do a pivot table within a pivot table (I don't think it's possible). The other complication is that some of the data (i.e. accounts with alerts) will change from month to month, which is why it would be ideal if I could keep a master list of all accounts so when one account drops off or comes on any alert list, I don't have to do the whole spreadsheet over (because the ones that stay on from month to month will still be there). This part isn't crucial (i.e. I could get rid of spreadsheet #1).
There are a few other things but that is the basics of what I need. I already use vlookup extensively and while it works well for what it is, my resulting spreadsheets are a mess and take hours to create. It truly is a nightmare.
Based on what I've said, what solution above would work best? (Obviously, the best answer is to get better software, which I am also working on and may be awhile so for now we're stuck with what we have).
posted by triggerfinger at 6:04 PM on January 12, 2016
I briefly tried Access but realized I didn't really know what I was doing so stopped (but I'm open to trying again if that's the best option).
A brief (and simplified) description of what I'm trying to do: say I have four spreadsheets. The common info on all spreadsheets is account name and number. The spreadsheets are accounts that have been flagged with various alerts.
Spreadsheet #1 has all accounts listed (master list)
Spreadsheet #2 has all accounts with a single specific violation. Additional info: violation name and a quantity. There might be multiple items (lines of data) that trigger this violation, so I need each line rather than one general flag on the account.
Spreadsheet #3 has accounts falling outside a certain variance. Additional info: each account's specific variance target thresholds and the amount outside the variance
Spreadsheet #4 has all accounts with something unverified. Additional info: what is unverified and an actual degree of something
Not every account will have a violation, and some accounts will have one or more violations.
I want to have one spreadsheet that is sorted by account which has all violations (if any) by that specific account grouped together. The violations can either be on rows or in columns. The problem I'm running into is that each alert has different info contained within, which is why I was trying to figure out how to do a pivot table within a pivot table (I don't think it's possible). The other complication is that some of the data (i.e. accounts with alerts) will change from month to month, which is why it would be ideal if I could keep a master list of all accounts so when one account drops off or comes on any alert list, I don't have to do the whole spreadsheet over (because the ones that stay on from month to month will still be there). This part isn't crucial (i.e. I could get rid of spreadsheet #1).
There are a few other things but that is the basics of what I need. I already use vlookup extensively and while it works well for what it is, my resulting spreadsheets are a mess and take hours to create. It truly is a nightmare.
Based on what I've said, what solution above would work best? (Obviously, the best answer is to get better software, which I am also working on and may be awhile so for now we're stuck with what we have).
posted by triggerfinger at 6:04 PM on January 12, 2016
Best answer: I am an IT trainer, but not your IT trainer :-)
Having seen your update I agree with posters above that PowerPivot would probably be the best thing to use.
If you want to have a look at it yourself there are some great free tutorial videos here: PowerPivot
However this would still take you a little while to study & then construct some solutions.
Also as others have said, the IT Training company at which I work (probably of no use to you, since its in Romania), offer my services out as a consultant at an hourly rate. One person has brought me something that sounds quite similar to your problem in the past, and so we put together a solution & I taught her how to do it as well.
I'm not sure what rates are locally to you, but it sounds like it may be worth it to you just to spend the money for a couple (or few?) hours of consultancy & have the problem resolved, rather than waste your time beating your head against it any more.
posted by cantthinkofagoodname at 2:52 AM on January 13, 2016 [1 favorite]
Having seen your update I agree with posters above that PowerPivot would probably be the best thing to use.
If you want to have a look at it yourself there are some great free tutorial videos here: PowerPivot
However this would still take you a little while to study & then construct some solutions.
Also as others have said, the IT Training company at which I work (probably of no use to you, since its in Romania), offer my services out as a consultant at an hourly rate. One person has brought me something that sounds quite similar to your problem in the past, and so we put together a solution & I taught her how to do it as well.
I'm not sure what rates are locally to you, but it sounds like it may be worth it to you just to spend the money for a couple (or few?) hours of consultancy & have the problem resolved, rather than waste your time beating your head against it any more.
posted by cantthinkofagoodname at 2:52 AM on January 13, 2016 [1 favorite]
Best answer: I would try and avoid doing this in Excel to be honest.
What you are describing, to my eyes is a relational database with 4 tables.
With a database you can keep all the data and then query it to get what you need very quickly.
My go to tutorial for databases is:
GalaxQL which teaches you SQL (the language that databases generally speak)
Microsoft Access is a relational database manager, which will do this (I don't like it much, but for something of this scale it should be fine). Access is based on SQL but generally abstracts it away behind a user interface. It's still the same principles though.
It's definitely the most intensive and disruptive solution, but I believe long term it's the best way.
posted by Just this guy, y'know at 7:03 AM on January 13, 2016
What you are describing, to my eyes is a relational database with 4 tables.
With a database you can keep all the data and then query it to get what you need very quickly.
My go to tutorial for databases is:
GalaxQL which teaches you SQL (the language that databases generally speak)
Microsoft Access is a relational database manager, which will do this (I don't like it much, but for something of this scale it should be fine). Access is based on SQL but generally abstracts it away behind a user interface. It's still the same principles though.
It's definitely the most intensive and disruptive solution, but I believe long term it's the best way.
posted by Just this guy, y'know at 7:03 AM on January 13, 2016
Response by poster: Okay, I got PowerPivot and spent the first five hours of my day watching the videos linked by cantthinkofagoodname and trying to figure out how to use it with my actual spreadsheets (I did learn a reasonable amount). Then I somehow managed to do something with it that crashed my computer so I gave up on it for the day.
It seems like it might be the solution I'm looking for. The vlookup-like capability is great and I think it will let me do pivot tables within pivot tables, if I can ever get one done without crashing everything. Access would likely be a more comprehensive and all-around better solution, but I have to consider the time I'll need to spend learning it. I'm starting with a good familiarity of excel and while I've used access and even learned how to do basic things with it in ancient times when I was in college, I can pretty confidently say I'm starting from a zero knowledge base. After reading Just this guy, y'know's comment, I literally googled "how to do a relational database with 4 tables" and a bunch of things came up that said things like SQL and CRUD and I was like hahaha no and closed it.
If I can learn it well enough in, say, under five hours (ten max), I'll put the time in, but anything above that and the amount of time I'm putting into it starts to seem excessive, and I'd rather just keep it in our current mode of using multiple spreadsheets to capture information that would be much better if it were all in one place.
I think I really just need some help on this. A consultant is probably the best way to go - I feel like they could show me fairly quickly and easily what I need to do and once I know how to do that, I can learn a bunch of other additional stuff when I have time (which is how I learned excel in the first place). Thank you everyone for your help!
posted by triggerfinger at 1:55 PM on January 13, 2016 [3 favorites]
It seems like it might be the solution I'm looking for. The vlookup-like capability is great and I think it will let me do pivot tables within pivot tables, if I can ever get one done without crashing everything. Access would likely be a more comprehensive and all-around better solution, but I have to consider the time I'll need to spend learning it. I'm starting with a good familiarity of excel and while I've used access and even learned how to do basic things with it in ancient times when I was in college, I can pretty confidently say I'm starting from a zero knowledge base. After reading Just this guy, y'know's comment, I literally googled "how to do a relational database with 4 tables" and a bunch of things came up that said things like SQL and CRUD and I was like hahaha no and closed it.
If I can learn it well enough in, say, under five hours (ten max), I'll put the time in, but anything above that and the amount of time I'm putting into it starts to seem excessive, and I'd rather just keep it in our current mode of using multiple spreadsheets to capture information that would be much better if it were all in one place.
I think I really just need some help on this. A consultant is probably the best way to go - I feel like they could show me fairly quickly and easily what I need to do and once I know how to do that, I can learn a bunch of other additional stuff when I have time (which is how I learned excel in the first place). Thank you everyone for your help!
posted by triggerfinger at 1:55 PM on January 13, 2016 [3 favorites]
This thread is closed to new comments.
posted by selfnoise at 1:27 PM on January 12, 2016 [3 favorites]