+ Reply to Thread
Results 1 to 2 of 2

Eliminate extra spaces so duplicates can be removed

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2010

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    MS-Off Ver
    office 365

    Re: Eliminate extra spaces so duplicates can be removed

    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

    I created column M to make the HCPCS codes consistent with this formula
    Formula: copy to clipboard
    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: copy to clipboard
    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Eliminate extra lines within cell
    By shre0047 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-23-2016, 12:38 AM
  2. [SOLVED] Extra space or spaces in Text in Forum Post. BB Code for extra space ?
    By Doc.AElstein in forum The Water Cooler
    Replies: 21
    Last Post: 03-02-2016, 02:59 PM
  3. [SOLVED] Preceding Spaces in Excel Formula Removed When Copied
    By JYTS in forum Excel General
    Replies: 16
    Last Post: 10-12-2015, 01:24 PM
  4. Intentional blank spaces being removed during mail merge
    By birdablaze in forum Word Formatting & General
    Replies: 15
    Last Post: 12-23-2014, 10:30 AM
  5. [SOLVED] Name Formatting: Is there a better way to eliminate spaces?
    By tjuice in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2014, 06:34 PM
  6. Replies: 2
    Last Post: 12-08-2009, 03:41 PM
  7. Macro to eliminate extra space
    By kleivakat in forum Excel General
    Replies: 3
    Last Post: 01-31-2006, 08:25 AM

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1