Duplicate Values Exist Upload Error

Error message: Invalid - Duplicate values exist in the upload...

File upload failure caused by duplicates in the upload sheet

Description:

The File Upload Center under the Planning tab in Catalyst provides some good detail about your uploads, including whether or not the upload completes and if there are any errors or warnings about the data you're trying to upload. One such error we often see is the Duplicate Values exist error. 

Solution:

Before the system will accept your file, you'll first need to root out your duplicate records and remove them from the upload sheet: 

1. In the upload sheet, select all of the data, including headers and place a pivot table over it in a new sheet. The shortcut is Alt + N + V and then Enter.
   
2. In the new sheet where your new pivot table is, drag every field other than your months into the Rows area and then add your months to the values area. Ignore all optional fields (e.g. Company Name). Optional fields will say optional in the title, so they should be easy to recognize. You're essentially recreating the upload sheet as a pivot table. 
   
3. The result should now be a similar looking table as your upload sheet, but it will likely have fewer total rows than your upload sheet. This is what we want to see, because the pivot table has aggregated the duplicates producing fewer total rows. 
4. That is the proof you need to confirm you indeed had duplicate records in your upload sheet. 
5. Next step is to copy and paste (as values) everything other than the headers from your pivot table and replace what's in the original upload sheet, again leaving the column headers alone. 
6. You'll first want to clear out the original file, leaving the headers alone. Do that by highlighting all the cells of data below your headers and hitting Delete on your keyboard. 
7. Copy your data from the pivot table by selecting all cells below the headers. Copy and paste one column at a time and paste as values (Control + Alt + V). 
8. Tidy up the sheet by formatting all numbered columns to Number format and text columns to Text format. Be sure to eliminate decimal spaces if they are added to account numbers. Correctly align your cells if there are inconsistencies.
9. Clear out any cells that say "blank". This is common to find because a pivot table will pull in the word "blank" from a cell that has nothing in it. Since we would prefer an empty cell than the word "blank" you'll want to replace any cell with the word "blank" in it with nothing, just clear it out. There are several ways to do this, which you'll see below. The most common is to simply use the Control + F (find and replace) shortcut.  
   1. In the sheet, type use Ctrl + F to pull up the Find dialog. Type in "blank" in whichever way it's seen in the cells you want it removed. Leave the "Replace with" field empty. Click Replace All and you're done! You can skip to the last step in this article. If you'd like to learn other ways to remove the word "blank" from your data, read on to the next two steps.
      
   2. Click the filter button at the top of each column and select only blanks if they exist. Then clear them out by highlighting them using the following shortcut command (control shift down), then hold down Alt and press ; (which selects only visible cells), then hit delete on your keyboard. 
      
   3. Another way is to create a copy of your pivot table in a couple of columns over to the right using a formula which will replace all blanks with an empty cell.
      
      That formulas is =IF(A2="(blank)","",A2). Copy all your headers from the pivot table and paste them nearby the pivot table. Then use that formulas in the first cell of the new table and copy the formula across and down to fill in your cells. You now have a table you can copy from that has no blanks.
10. You're done. Now save and upload via the EBM Office Bridge ribbon or via the File Upload Center.