+ Reply to Thread
Results 1 to 2 of 2

Convert rows to columns and repeating the fixed value

  1. #1
    Registered User
    Join Date
    12-21-2018
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    2

    Convert rows to columns and repeating the fixed value

    Dear user,

    After doing calculations with pivot tables, I have a large set of data that I have to convert to a specific format.
    The pivot table looks exactly the same as the picture:
    two-dimensional-pivot-table.png

    What I want to achieve is:
    - For every specific Fruit in column B to H;
    - I want to repeat the country and put the specific fruit in column B;
    - AND the value of the fruit in column C;

    The end result has to look like this:

    Australia - Apple - 20634
    Australia - Banana - 52721
    Australia - Beans - 14433
    ...

    Ofcourse I can do this manually, but my dataset is too large for it.
    Does anyone know how to do this automatically?

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Convert rows to columns and repeating the fixed value

    Hi,

    Drag the Fruit field from the Column Labels pane to the Row Labels underneath the country field. This will indent the fruit in a single columns with the country.

    To display in a column of its own then right click the PT choose PT Options, Display...Classic PT Layout.

    To repeat the country right click the PT, choose field settings, Layout & Print and then Repeat column labels.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. Replies: 13
    Last Post: 10-13-2015, 04:17 AM
  2. Replies: 7
    Last Post: 01-05-2015, 08:00 AM
  3. Replies: 6
    Last Post: 12-06-2012, 11:05 AM
  4. [SOLVED] Convert Repeating Fields (Columns) to Rows
    By KQ3884 in forum Excel General
    Replies: 1
    Last Post: 04-05-2012, 02:47 PM
  5. Convert columns into rows with a repeating attribute
    By shaunriordan in forum Excel General
    Replies: 0
    Last Post: 10-19-2011, 01:12 AM
  6. Convert rows with repeating data to separate rows
    By lancemonotone in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-25-2010, 07:10 PM
  7. [SOLVED] How to convert a repeating row of information to fit in 3 columns
    By dawn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-17-2006, 10:40 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