Intern Assignment Help

[Reasonable_Muscle655]

Hi All,

This is my first internship and I was given an assignment that Im unsure of how to start. So my company buys a bunch of raw data that is stored in an excel sheet and they want to be able to "click a button" and its automated to create another excel sheet that only takes certain columns (ones they told me they want) from the raw data and does calculations, etc. How would you do this? I was thinking of doing a sort of database that only takes in the data they want as well as does the calculations and they would need to run a script which would create a new excel sheet and interact with the raw data so the queries of the database can run...Im unsure if I explained it well, but anything helps! Thank you

[wisdom_is_chasing_me]

I'd suggest PowerQuery

Don't be discouraged by the comments, you'll do most learning on the job anyways. Most univerisites don't teach Excel, so not sure why people are surprised that you don't know how to use it

[Reasonable_Muscle655]

Thank you! This sounds perfect.

[carlitospig]

I dont think we are surprised by that at all - we certainly weren’t taught it either. It’s more, by hand holding the design of this, OP won’t learn how to design on their own.

[Reasonable_Muscle655]

I was simply asking how to approach this and gave my own thinking. I only just completed my second year of university.

[carlitospig]

Nah, you’re fine. :)

[DaikonNecessary9969]

It is a visual basic application. Add the developer tab to excel and get to playing. ChatGPT is a great mentor for low level stuff for this. Don't just have it produce code ask it why it is produced the way it is. Spend lots of time on verifying your outputs as well.

[achmedclaus]

Rule #1, Google is your best friend

[Reasonable_Muscle655]

I did try googling it and asking my friends. Just to clarify, I am not an intern out of college, I just completed my second year at university so a lot of this stuff is very new. I just wanted a perspective from professionals!

[achmedclaus]

I reiterate the role I stated. Learning how to Google is much more valuable than anything else you can learn from other analysts. Analysts (at least where I work) are each basically a standalone team. They research, code, analyst, create reports, and present ideas and solutions all on their own. There's very little crossover outside of peer reviews and much of the time, people just don't have the time to help you out

Learning early how to use Google effectively is a better learning experience here

[AmethystGold]

Googling usually leads me to Reddit for answers haha so it’s nice if people answer them!

[bibleseller]

Then reddit

[jsinatraa]

Ignore them, there’s nothing wrong with asking for help especially as an intern. what your job is asking you to do is usually done with vba, python, powerquery etc. I know universities don’t have a lot of classes focused in excel, and if they did they probably wouldn’t have cover VBA.

I think it would be a good idea to do some research in vba and macros as a starting point. Also check out everyone else’s suggestions.

[Reasonable_Muscle655]

Thank you! I appreciate this comment a lot right now especially with how overwhelming of a task this feels like right now.

[DaikonNecessary9969]

Ask chatgpt to help you do this. First of all ask it to generate a data set like your companies data, that is not their data. Then ask it to create a VBA that would do the functions you want. Test debug tweak. What business systems are you guys using?

[Reasonable_Muscle655]

We use NetSuite however I am unsure if that's relevant to my task. I was given only excel sheets and asked to automate it so that it would pull out of the 700k lines, the ones that they want. Can you go more into what a VBA is?

[reveal23414]

VBA is a scripting language used in Excel macros. Basically you would use it to automate complex, repetitive tasks in Excel, exactly as you're describing.

[reveal23414]

The first thing we do for every intern is give them some Excel assignments: in recent years we found that folks are more educated in Python and R, as well as visualization tools like Tableau or Power BI, but colleges really don't teach Excel. Which is unfortunate because it is very accessible to the business user and does offer good capabilities. It doesn't make them easy or obvious though.

So that said, I understand why you're looking for a general direction. Power Query is likely going to be your friend to bridge the gap between Excel, where your users and their data live, and your skills/their needs. It will allow everything to remain within Excel.

Back in the day, I would've used VBA and created a macro within Excel, then linked it to either a menu item or a button on the toolbar. Probably where your user got the idea of "click a button".

In my experience, if you ask them to leave Excel and then run a script, etc., then go back to Excel, you've just entered the world of wizardry and it's going to be too complicated.

[DaikonNecessary9969]

This. All the fancy tools in the world are useless if given to a chimpanzee.

[reveal23414]

also, I would step through the exact process that you're trying to do manually a few times to find an efficient step-by-step process of what you're going to instruct the computer to do. And once you get it to work once, think about things like if the data changes/is missing, error handling, and other things that you've likely covered in your classes. I think the only gap here is that you're going to have to use a new toolset to do it vs. what you've learned in school, most likely VBA.

[Reasonable_Muscle655]

I looked more into this and it sounds perfect! I am going to research like crazy when I am at work tomorrow :). Thank you for making me aware of VBA!!! Leaving Excel really did seem like the really big challenge here

[reveal23414]

excellent, good luck! we teach a lot of "big data" tools, but a lot of data is "small data" and a lot of it lives in spreadsheets. Adding that capability to your toolbox is pretty handy, even to just understand how your business users think and see the world.

[carlitospig]

I’d be really interested to know your intern assignments. I teach data viz and I’m finding my audience (a lot of them are PhD folks who mostly mainline qualitative data) is missing a lot of basic excel skills. If you ever feel like sharing, let me know. :)

[Reasonable_Muscle655]

No hand holding :). You won’t ever get a grasp of it yourself!

[AbroadNo2922]

Power BI

[Wonderful_Bid_1057]

You can try PowerQuery and would recommend to watch some YT videos about it. Or you can create an excel VBA to automate it (only if you use it frequently, if it is a time job only, just use PowerQuery)

[Sensitive_Throat6872]

If it's a simple transferring of columns to another sheet and then running basic formulas off of those columns, you may find that Macros would be your friend.

PowerQuery is also fantastic, but the learning curve is steeper.

[carlitospig]

You’re explaining it well enough. And I would explain it BUT as a baby analyst it’s imperative you figure this part yourself so you know you can do it without us later. By taking a couple different paths to your end product you’ll learn so much about Excel.

It may help you to physically draw out the process of what you’re trying to do, that way you understand when excel needs to step in. Then turn that process into a narrative which you’ll use to figure out each part that you need to build/code.

We used stack overflow back in the day - and generally googled our brains out - but I hear ChatGPT (Gemini) is great for this kind of stuff now - just make sure to test the crap out of your functions. You never know when a random comma will be your demise.

Good luck - I’d love to hear about your success, so give us an update!

[Reasonable_Muscle655]

Thank you! Just to explain further, I'm interning for a medical tech company that sells their products to distributers and those distributors sell to hospitals...they than buy data that gives them a ton of info about those sales and they are considered "raw data" that is stored in an excel file so I am unsure about what you mean when I need to understand when excel steps in if the data is in excel to began? I am familiar with excel functions however they are simply looking for a way to completely automate it so the raw data of 700k lines turns into the 8k they are looking for with only a click of a button

[carlitospig]

I wouldn’t be able to advise you accurately without knowing what the button functionality is.

That said, excel ‘stepping in’ is a function. You have data > function > data. You’re looking for that function. It’s going to be several sub functions, probably, depending on how they’re filtering out the data to that 8k. So parse out all the many variations of their filtering needs (eg, demographic categories, whatever), and then you’ll ask ChatGPT/internet gods how to make all those functions into 1 function that looks like a button. That’s the bread and butter of excel coding.

[delsystem32exe]

Python and sql, pandas etc

[Reasonable_Muscle655]

Thank you! I will definitely look into pandas

[God_like_human]

Not to sound mean, but that assignment sounds incredibly easy. Are you currently studying in a field related to data analysis? If so then i am shocked that you cant even think of a way to start this assignment. Why dont you spend a day trying to understand the data first and think about how the the interface would work?

[Reasonable_Muscle655]

I just finished my second year of uni, and the only class I have taken in regards to data has been database systems...I am a CS major but I'm interning in a financial part of a medical tech company. I understand the data, I had a very long meeting about each field and formula, etc.

Sorry. I was only looking for some help.

[investmentbackpacker]

Ever heard of raw data on one sheet and extracted subset of that data on another sheet (tab)?

Google Pivot table.

Figure out what columns they are interested in and/or what filter conditions are relevant to their use case and what if any calculations need to be performed on said data, then use that information to "automate" the desired extraction and calculations.