This article is basically a reference for all Excel formulas that are helpful when working with databases and projects.
Area Code Reference List
For this formula:=VLOOKUP(VALUE(LEFT(D4,3)),A4:B413,2,FALSE), it creates a reference list for all area codes and states and matches them by what you have on column D to column E.
You can do this by going to E4 and click and pull down from the small square on the right to populate what's on column D, such as 818 from the example below.
Show 15+ Digit Numbers or any amount
Excel by default will show something like this : 1.2346E+E
if you try to type in a 15+ digit like this : 12345678901234500000
You will need to add a formula like this : =LEFT(A1,20)
(In the cell A1, shows 20 digits starting from the left.)
This is also useful when doing something like area codes: =LEFT(A1,3)
This will only show the first 3 numbers.
Reference 2 columns and match to a 3rd column to give you a result
In this formula which is =INDEX(B:B,MATCH(D3,A:A)) , you will look up columns A and B to match column D.
This is useful when you have a bunch of names or phone numbers and you don't know what it belongs to.
In this example, the formula is added to the last column (column E) and the formula is checking A:A, B:B, and the entry D3.
Calculate Short Duration for Reports
The purpose of this article is to calculate the ratio of calls that are being billed as 6 seconds.
This is shown when getting a .CSV file that shows a billsec column, such as the one below.
To calculate this, you will need to go to Excel or Google Sheets and add the following formula.
=COUNTIF(A:A,6)/COUNTA(A:A)
A:A means the range
6 is what's in the cell
This just takes the cells that are one value and divides it from the total that has digits.
You can also try =COUNTIF(A:A,6) to show all cells with 6 in them.
=COUNTA(A:A) will count all cells with numbers in them.
Separate large CSV files from Terminal (Mac ONLY)
This will show you how to separate a large CSV file. Especially useful when you have more than a couple million leads or numbers.
First, create a folder and go to that folder in Terminal by typing "cd " without quotes to start from that folder.
Once done, type this in:
split -b 5m millionsofleads.csv
What this will do is start the program called Split and separate your leads/rows by blocks of 5mb each for the CSV file you have in the folder.
You can go by line if you type this in:
split -l 250000 millionsofleads.csv
This will create files with 250k rows.
Once this is done, you will see these type of files:
You can leave it like this or rename them by adding this line of code after you do this:
for i in *; do mv "$i" "$i.csv"; done
Then, it will rename all the files with a .csv extension.
