Percentage of match for two columns in excel? (Fuzzy Matching?)

[undergroundboy]

Hello!

I just recently got a new job as an analyst and I am having trouble calculating the percentage of matches of specific names (i.e. Albany and Eden, Elkton and Golden) between two columns. Attached a sample problem if anyone can help guide me? I have a general idea of using match and IF but don't know how to start exactly. Thank you!

 

[Nunyabinez]

You need to use the COUNTIF() Function and the exact() function.
Here is how to do it. In an empty cell enter =EXACT(<first cell>,<second cell>) this will return True if they are exact and False if they differ in any way.
Then in the cell next to that put =COUNTIF(<cell with the exact formula>,TRUE)
Drag the two formulas to the end of your columns.
Then you will have a column with Trues and Falses and next to it a column with 1s and 0s. To get a percentage you just need to take the total number of 1s, divided by the total number of entries and that will give you the percentage of cells where the two values match. I assume you can figure that part out by yourself.

 

[Moakesy]

If I understand your screenshots correctly, you need amounts (well, percentage) by courier, by destination yeah?

So it's two dimensional data......a table that shows carrier down the side, destinations across the top? Or even by each carrier, by from and two location. If so, a pivot table will be the quickest way to create the data, which you can then use to calculate percentages.

I notice the brief also says about a date range....how important is this?

All of it is do-able, and could be surprisingly quick. Also, what version of excel are you using, as later versions can do some sexy stuff that looks amazing but is a piece of cake! It's an easy way to make a great impression in your new job.


EDIT : Just notice that they also have left a column blank on the Actuals tab, assume they want you to use WEEKNUM function in there? If so, then put =WEEKNUM(a2) in cell B2 and copy it down. This will look at the date in A2 and say if it's week1, week 2 etc of the year.