+ Reply to Thread
Results 1 to 18 of 18

When I create a Pivot table I it combines items with same Figure number :(

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    When I create a Pivot table I it combines items with same Figure number :(

    Hi,

    Attached is a very small Excel that is an output from a program that I use. I want to transform the information into a format that I can then input into another Excel without having to perform multiple "Copy & Paste" operations.

    The 1409 sheet is the original data. Sheet 1 is how my pivot table presents it whilst Sheet 2 is how I want it to look. I even added a numerical element to the subject to try and achieve the correct look but Pivot seems to ignore this.

    I'm hoping that as the data is not complex (only 8 columns) then someone can advise what I should be doing.

    Thanks in advance,

    Cheers

    Paul

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: When I create a Pivot table I it combines items with same Figure number :(

    You need to add the subject to the row field area too and then reformat the table in a tabular layout, as attached.
    Rory

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Hi Rory,
    Thanks for having a look so quickly and taking the trouble to provide the example, it's exactly what I want!

    It's just however I adjust the various fields between the Filters/Colums/Rows/Values I just end it with Valve numbers occupying single columns, rather than the rows you have.

    Is that solved by reformatting the table? And if so, how do I do that? Apologies, never really used Pivots before

    Cheers

    Paul

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Once you have the fields arranged in the Field grid the same way as in my example, you need to change the pivot table layout (Design tab) to Tabular, then turn off Subtotals and Grand Totals.

  5. #5
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    You absolute F**king STAR!!!! Brilliant, thanks soooooo much!!

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Glad to help. Nice to see East and West come together.

  7. #7
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Lol, I saw that too....

    Quicjk question, can you embed a pivot table so that it will run on any info that you put onto the sheet or can you only run the pivot every time/

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: When I create a Pivot table I it combines items with same Figure number :(

    If you make the source data into a Table and then base the pivot off that table, then all you need to do is refresh the pivot table after you add (or remove) data.

  9. #9
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Hi Rory,
    Hope you manage dto enjoy the glorious sunshine at the weekend!

    I been practising and have not got the standard pivot off pat

    When you say "make the source data into a table" - how? and how do I refersh a pivot table? (bearing in mind that it took me a while to find the design tab as never used/seen it before!)

    Cheers

    Paul

  10. #10
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    *have now - bloody thumbs!

  11. #11
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Quote Originally Posted by Strevslondon View Post
    When you say "make the source data into a table" - how?
    Select the data and press Ctrl+T

    and how do I refresh a pivot table?
    Right click it and choose Refresh.

  12. #12
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Hi Rory....Me again!

    Step 1) Make data into a table - Cntr + T, Done and that gives me a table with my 3 headers (Subject, Valve No. & Quantity) in dark blue with a dropdown box arrow next to each with the rows alternating between light blue and clear....check.

    Step 2) Highlight and capture the headings in my Pivot table (Sub, V. No & Qnty) and right click, then refresh (or refersh from the tool bar) ..... The data changes to take into account new input but has lost the correct order.......bummer!!

    Any Idea what I'm doing wrong? Or am I missing a step? or.......???

    Cheers

    Paul

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: When I create a Pivot table I it combines items with same Figure number :(

    What do you mean by the correct order?

  14. #14
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Hi,

    Below is original, note how all is in number order based on Subject column

    Pivot_CF.PNG

    But when I change/alter data in the table and then refresh pivot, the numerical order is lost, as below

    Pivot_W.PNG

  15. #15
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: When I create a Pivot table I it combines items with same Figure number :(

    You need to actually sort the pivot table on the subject column. That will then persist between refreshes.

  16. #16
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Aha!! So simple........thanks again

  17. #17
    Registered User
    Join Date
    06-03-2013
    Location
    Lancing, West Sussex
    MS-Off Ver
    Microsoft 365
    Posts
    19

    Re: When I create a Pivot table I it combines items with same Figure number :(

    Nope....still not doing it right

  18. #18
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: When I create a Pivot table I it combines items with same Figure number :(

    In what way?

+ 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. Create table in separate tab summarising total number of items
    By Holalola83 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2017, 07:23 AM
  2. Create a list of row items from a changing pivot table
    By bnels in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2017, 06:51 AM
  3. Create a table/pivot table using different items items from a column
    By stevetton in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-28-2016, 10:30 PM
  4. [SOLVED] My pivot table is treating a number as two separate items
    By dcoates in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-08-2015, 03:04 PM
  5. Replies: 5
    Last Post: 07-11-2014, 03:57 PM
  6. Pivot table to find number of items moved given in an amount of time
    By dsoeder in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-20-2013, 01:16 AM
  7. Replies: 2
    Last Post: 09-26-2012, 01:41 PM

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