Eliminate extra spaces so duplicates can be removed

I am creating a CPT code table with the 2015, 2016, & 2017 tables combined. What I am hoping to do is eliminate any duplicates. The problem that I am having is that if you look at the short descriptor column you will see what appears to be extra spaces at the end, so when removing duplicates, it doesn't remove them. I have tried to use the TRIM function, but to no avail. The goal is to have take all 3 years, pivot off the data, and use as my final CPT code table. That way, if there was a code back in 2015, that is not valid in 2017, it will still be on my table. If the CPT code was valid in all 3 years, and the description stayed the same, I want to eliminate 2 of them. You will also notice in column K that I have the type listed. Some of the CPT codes are type 1, but I'm trying to make them all consistent so that when I refresh my pivot, they won't show up as 2 separate codes.

Have a look at the attached file to see if this gives you what you want.
Rather than focus on the descriptions and trying to fix them, why not use the HCPCS codes and apply the 2017 descriptions to all years

Sheet1
I created column M to make the HCPCS codes consistent with this formula

Formula:

Please Login or Register  to view this content.

then copied column M and Pasted Values to Column N

copied Description & Date to columns O and P

Copied columns N,O,P and pasted to sheet2 and then FILTERED on 2017

Copied FILTERED 2017 and pasted to Sheet 3 - this is now our look-up table

Back on sheet1 in column L , used the lookup table to provide consistent descriptions for all years

Formula:

Please Login or Register  to view this content.

If it's what you want then next step is to copy column L and paste VALUES to column C



To acknowledge help give by anyone remember to click on *Add Reputation