Results 1 to 4 of 4

Thread: Create a Pivot table

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    8
    Rep Power
    0

    Create a Pivot table

    Hello,

    I do require a help.
    Please find the attachment in which there are two sheets raw data and required format.

    I want to apply pivot table to the raw data in such that results will be as mentioned in the second sheet ( Required format).

    Please help

    Thanks & Regards

    NItin Shetty.
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Nitin,

    I don't think you'll able to create a Pivot like that.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    To Get THe Format you want you will want to create a helper column.

    You can paste a conatenation formula in one Column lets say E. This formula will be in E2 =CONCATENATE(B2,C2,D2).

    Once this is done you will have a new column with 3 values run together. It appears that you need only the unique combination of these values so next we will copy column E and paste special values into column F.

    Now that you have pasted these values you will want to eliminate duplicates.

    Highlight Column F then go to the Data tab. Remove duplicates. You now have only unique values

    Now you have only the unique values that you wanted on your formatted page. Next you will need to separate these 3 strings out again.

    In G2 put this formula =LEFT(F2, 6) and drag down to the last value of unique values.
    In H2 put this formula =MID(F2,7, 5) and drag down to the last value of unique values.
    In I2 put this formula =H2+0 and drag down to the last unique values. Now select column I:I and format as date. Copy and Paste as Value in place(same Column). THen delete columns H it is no longer needed.
    In I2 put this formula =RIGHT(F2,LEN(F2) -11) and drag down to the last unique value.
    Copy and Paste Column I's Values In Place (Same Column)

    Now you should have the information you need. Copy and Paste Under your headers....

    This process is fairly easy to automate with the macro recorder and a little editing. A Pivot Table is not needed.
    Using Excel 2010

  4. #4
    Junior Member
    Join Date
    Dec 2012
    Posts
    8
    Rep Power
    0
    Actual i add data daily to the master file.

    Hence needed a pivot table so that whenever i refresh it it will give the data.

Similar Threads

  1. Preparing Trial Balance Using Pivot Table
    By Zaigham in forum Excel Help
    Replies: 4
    Last Post: 05-14-2013, 11:57 AM
  2. Lookup and Count Using Pivot Table
    By RobExcel in forum Excel Help
    Replies: 2
    Last Post: 12-21-2012, 11:08 AM
  3. Pivot Table - Volume by Day and Half Hour
    By Ian Herndon in forum Excel Help
    Replies: 9
    Last Post: 07-07-2012, 10:04 PM
  4. Pivot Table Count No of Items per Category
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-04-2012, 10:49 PM
  5. Filter more than one pivot table at one time
    By larryt1940 in forum Excel Help
    Replies: 8
    Last Post: 05-04-2012, 06:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •