Pasting into pivot database causing duplication

[NMullis]

Good morning,
There is probably a simple solution for this, but I have yet to find it. I have a pivot table that I need to be able to update the database information by pasting into or over existing data. Whenever I do, the pivot table will refresh and include the data, but my field names all become duplicated, even though they are exactly the same.

Am I doing something wrong when pasting or is there a way to override this?

Many thanks,
N

[Ron Coderre]

Can you post more details about what you're doing?

It sounds like you are replacing the original data by copying a new source and pasting it over the old data.

If that's true, what's the new source? a database extract? something else?
Are you replacing ALL of the data, or just some or it?

Is there any chance the new data has extra spaces after the values?
Example: "Product 1", "Product 1 "

Try this formula:
=EXACT(item_in_the_list, duplicate_of_that_item)
Does it return TRUE (indicating a match) or FALSE (indicating a difference)?

Let us know what you discover?

[NMullis]

You are correct that I am replacing original data, or appending to it.

I have tried it both with a pivot table in the same spreadsheet and have saved it as a template. The data is retrieved from a SAS query and pasted into the database. When originally creating the database I had exported the information into Excel and then created the pivot table. When I paste it, I copy directly from the query result to Excel and have found, thanks to your formula, that there are trailing blanks in the first creation and when copying and pasting the blanks are not there.

I had thought that saving my spreadsheet as a template would overcome this because I have deleted the data and choose to enable the automatic refresh, which clears the pivot table. Or so it appears. Actually the fields are all still there, just with no data behind them. That is, when I click on the drop down arrow of my pivot table, the previous fields (names of customers) still show up, even though they aren't in the database.

Is there a way around this? I am trying to take my data directly from my query into my pivot database and have marry up perfectly, without having to recreate the pivot table each time. Any ideas would be appreciated.

Thank you!
N

[Ron Coderre]

Regarding obsolete field values appearing in the Pivot Table drop-downs
you have a few options:

1) The free Pivot Play add-in, located at Debra Dalgleish's website, may help:
http://www.contextures.com/xlPivotPlay01.html

That add-in's main function is to change the database source
for Pivot Tables that are based on external data....
BUT...it also has a button that removes those obsolete items from the drop-downs.

2) Various manual and VBA approaches, also located at Debra Dalgleish's website:
http://www.contextures.com/xlPivot04.html

Does that help?

[NMullis]

BTW, Ron, all is working wonderfully now. Thank you for your help! N