I work with thousands, or hundreds of thousands of assets every day.
I know exactly how difficult it is to keep tack of large amounts of data, and if I were to have to do it by hand I’d be tearing my eyeballs out regularly.
If you find yourself in this same situation – say you’re working with a large script, or keeping track of assets and their related integration events – you only need to learn two Excel formulas and your life will become literally exponentially easier.
Counting and comparison
The first formula is called =COUNTIF. It’s normally used for counting how many times a given entry shows up in a set of data.
So say you have Column A in your spreadsheet with the data:
If you used the following formula in a cell within Excel:
Then “2” would show up in the cell.
The first “parameter” (the stuff between parenthesis here) is “A:A” meaning everything in Column A. This is the range of data we want to sort through. “Apple” is our second parameter, and it is the thing we’re searching for.
Let’s look at another example…
So say you have a script where Column A is the scripted line and Column B is the character delivering the line.
If you want to know how many lines Master Chief has, your formula then looks like:
=countif(B:B, "Master Chief")
Neat, right? Same thing here as above – your range is everything in Column B, and your “search term” is “Master Chief”.
But let’s say you want to compare values…
For example, I have to do filename comparisons all the time. When doing localization recording, you need to make sure your files for French exactly match your files for English. Doing that by hand is a nightmare!
Instead, I start with putting all the filenames of the English files in Column A. Second, you put all the filenames of your French files in Column B. In Column C – you use the following formula:
While this one appears more complicated, it’s not crazy. Let me walk you through it.
Once again, our range to look through is everything in Column A. That was our English filenames.
Our “search term” is whatever filename is specifically in cell B1. That’s one of our French filenames.
The “>0” part of the code is telling Excel we don’t want a number, but instead a TRUE or FALSE value. So if Excel gives us anything greater than zero (meaning any result at all), our cell will show up TRUE. If our cell is zero or smaller, we’ll get FALSE.
So if I know I’m only going to have one result, and I want a simple “yes, we’ve got it” or “no, we don’t have it” – that’s what I use!
Grabbing data in other columns
What if you’ve got a script and you’re trying to copy the lines based on their filenames. Normally you’d find the filename, then copy and paste.
If you have to do this for 1,500 lines – have fun with your carpal tunnel.
Instead, you should try out =VLOOKUP.
VLOOKUP finds a piece of data and returns anything you want from a column you specify that’s to the right of that data.
So let’s say you have a list of filenames in Column A, and the scripted lines in Column B.
If you want to get the contents of Column B based on your filename, the formula would look like this:
=countif("Dexter_Ending_Final_Alt01.wav", A:B, 2, FALSE)
“What in the world is this magic, Adam?” you ask.
Again, not as crazy as it sounds. Here we go…
The parameter “Dexter_Ending_Final_Alt01.wav” is our search term.
“A:B” is our second parameter. This is the range we’re telling Excel to look through. Our filename is in Column A, and the text is in Column B – so we’re telling Excel to look through everything in Column A and B. If our text was in Column E, it would need to be “A:E”.
“2” refers to the relative column of the information that we want, based off our range. With our range in the example, we’re only giving Excel two columns to look through and our text is in the 2nd column – so this needs to be 2. In our “A:E” example, this number would be 5 if the text was in Column E. If we went super crazy and threw our filenames in B, so our range was “B:E”, then the number would be 4 – because we’re telling Excel to look only at 4 columns and our text is in the farthest one to the right.
“FALSE” means we want Excel to exactly match the search term in A1. This would be like putting quotes around your Google search. I literally never use partial match, so this is always FALSE for me.
Once written, Excel will look for our search term and then pull the text from Column B that is one cell to the right! So if our search term is in A34, Excel will give B34 back to us – no more copy and pasting!
I use these formulas nearly every day, and they save me hours of time, frustration, and headache.
Do you have any cool ones? I’d love to hear about them – @adamtcroft.
Copyright 2016-2017, Adam T. Croft, all rights reserved.