Alright, so unlike the other two guys, I work in corporate, which means I work with people who only use their computers to send emails, schedule meetings they'll cancel, and occasionally write a memo plus, because they're terrified of anyone associated with IT, they'll never ask for help. As such, I'm forced to do a similar job as my comrades using the tool that everyone has: Excel. Luckily, Excel has nearly infinite functionality for those that seek to explore further... Join me in this first of a series of indefinite length: Excel data manipulation, where we get you hooked on the gateway drug of data analysis, starting from absolute ground zero.
So, for a fitting first entry... how to turn Excel into more than just a fancy calculator.
Step 1: Click file, options, customize ribbon. Then see how there's one box on the right that isn't checked by default that says "developer", check it and see how deep the rabbit hole goes. Congrats, You're now officially more advanced than 85% of people who use Excel.
Step 2: Hit alt (or command...I guess) F11, don't ask why, just do it. This will open up the Excel VB Editor where you can actually engage in making Excel do whatever you want it to. Also, a fun fact for later, keyboard shortcuts are your best friend.
Step 3: Google for whatever it is you want to do followed by the word "Excel," because someone already wrote the code for it. Feel free to copy and paste it without mercy. This is how you learn, this is how everyone starts. Perhaps one day you will be able to write code that will assist another in this journey.
Step 4: After copying the charitable programmer's code, click back to that VB editor write click the name of the sheet you're working on, hit insert, then module, then paste that code in there. Then hit save, and feel free to close the VB editor.
Here's a useful tool to get you started: TEXTJOIN
----copy this whole thing----
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
ElseIf cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function
----stop copying---Step 5: Go back to that VB editor, right click the name of the sheet you're working on, click insert, and then paste everything in the last step into the window. Hit save, and close the editor. Don't save it in "Personal" just yet, that's for another time.
Step 6: start with the formula =textjoin (we'll talk more about formulas later), and watch as a formula that didn't exist before suddenly does because YOU DID THAT!
What this does is take the information in any row or column and combine it with a "delimiter" that you choose (the ";" semicolon is popular for reasons we will discuss later) into one giant cell. No longer do you need to type =concatenate(click, ";", click) 1000 times to combine the cells in a list!
Step 7: Save this file as a Macro enabled workbook, because if you want to use it again, your workbooks now need to be extra special.
Step 8: The next time you need to concatenate an entire table of values for whatever reason, plop that in there, and do what they assume is going to be a full day's work in 45 seconds.
So, I hope that helped just a little bit, and we'll be back next time to learn how to use Excel's overly complex series of formulas to do things that Excel really shouldn't be used for, but who are we kidding? It's all we've got!
Cheers,
- Snacks
