+ Reply to Thread
Results 1 to 14 of 14

Re-Post - Data Repeats in Pivot Table

  1. #1
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Re-Post - Data Repeats in Pivot Table

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

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,656
    Thread moved. Your question isn't about charting, right?

  3. #3
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    hi,

    check the length of the part # in column..

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


    U will get the answer

  4. #4
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Thanks, Kumar.

    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.

  5. #5
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    U can use TRIM function to remove the extra space that appear in the begenning or end of the string.


    =Trim(A1)

  6. #6
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    TRIM Doesn't work.

    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

  7. #7
    Forum Contributor
    Join Date
    01-18-2005
    Location
    Auckland New Zealand
    MS-Off Ver
    Office Professional 2007
    Posts
    295
    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

  8. #8
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Didn't work...

    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

  9. #9
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Bump...

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

    Thanks.

  10. #10
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    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))

  11. #11
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Those won't work...

    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?

  12. #12
    Forum Contributor
    Join Date
    01-18-2005
    Location
    Auckland New Zealand
    MS-Off Ver
    Office Professional 2007
    Posts
    295
    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
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    Try this


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

  14. #14
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    That did it!

    Thanks, Kumar. That fixed it up.

    Have a good one.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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