Re-Post - Data Repeats in Pivot Table

[[email protected]]

Good morning, All.

Technically, I don't believe my earlier post was a duplicat thread. I posted the original thread, then posted a LINK to the original thread. I didn't even ask the question in the link, so there was no way someone would have wasted time answering the question from there. I didn't think I was breaching protocal. Anyway, since both instances of the thread were closed, here is the re-post.

I've recently been trying to learn a little about pivot tables. In the example attached, I have 2 worksheets. One holds raw data collected from suppliers, the other holds a default pivot table created from that data.

My question is: Why are my part numbers repeating, in some cases, in my pivot table?

Thanks in advance for any insight.

Hutch

[shg]

Thread moved. Your question isn't about charting, right?

[Shijesh Kumar]

hi,

check the length of the part # in column..

=len(a1)
=len(a1)
...
...


U will get the answer

[[email protected]]

Thanks for that tip, Kumar. I see the problem. For some reason, some of the part numbers have a space at the end.

Have a good one.

[Shijesh Kumar]

U can use TRIM function to remove the extra space that appear in the begenning or end of the string.


=Trim(A1)

[[email protected]]

Trying to leave all the data in tact, I used the TRIM formula in the last column, then copied and pasted the values OVER the existing part numbers. For some reason the spaces remained at the end of the part numbers.

You can try it on the attached example, (top of thread), and see if you don't get the same results.

I also tried using replace, selecting the column and replacing a space with nothing, that didn't work either.

Whassup with that?

Thanks for your help, Kumar.

Hutch

[Mikeopolo]

By using the code() formula on the last character (the space) of your text, I note that it is code 160.

So to remove it, you could do this:

In a blank column, row 2, type:
=substitute(A2,code(160),"")

Copy that down the whole column, then copy and paste it (as values) over the original values. Then re-run the pivot table.

Regards
Mike

[[email protected]]

Thanks for the suggestion, Mike. I tried the substitute nothing for the code160 formula, but it didn't remove the spaces. After entering the code in a blank column, and copying it down the column, (the part numbers in the cell containing the formula matched the corresponding part # in column A), I copied only the values over the original part number. I re-ran the pivot table, with the same results. If I check the part numbers in column A, (AFTER pasting the values over them), they still show a blank at the end of the part number.

Any other suggestions?

Thanks in advance.

Hutch

[[email protected]]

Wanted to bump this one back up, hoping someone would know a way to remove all those spaces.

Thanks.

[Shijesh Kumar]

Hi,

I found that all the part ID's in your sample data have length = 10...
So I used the below formula and it worked fine...

=Left(A2,9)

U can also use below formula if u think that there can be part ids which is not 9 digit long...

=Left( A2 , len(A2)-1))

[[email protected]]

Thanks for the reply, Kumar.

Neither of those formulas will work.

The first, "=Left(A2,9)" won't work because there are several part numbers with more than 9 characters, and the formula trims the number down to 9.

The second, "=Left(A2,len(A2)-1))", won't work because it trims off the last character of the part number regardless of wheter it is a space or not.

Both good ideas, just not applicable in this situation.

Anybody else?

[Mikeopolo]

Hi there

I have attached your spreadsheet with the 'substitute' formula in column M, and the value copied to column N. I think it works corectly, but please check it.

hth

Regards
Mike

[Shijesh Kumar]

Try this


=IF(CODE(RIGHT(A2,1))=160, LEFT(A2,LEN(A2)-1),A2)

[[email protected]]

Thanks, Kumar. That fixed it up.

Have a good one.