+ Reply to Thread
Results 1 to 2 of 2

Converting Rows To Columns

Hybrid View

  1. #1
    Registered User
    Join Date
    09-23-2008
    Location
    montebello
    Posts
    1

    Converting Rows To Columns

    Sorry, did not explain my self. Please note that "," denotes a different column in Excel.

    Have a data set that looks like below.

    Sku,1/1/2008,2/1/2008,3/1/2008
    10054,5,11,18

    Need to convert to like below

    SKU, Period, Sales
    1004, 1/1/2008, 5
    1004, 2/1/2008, 11
    1004, 3/1/2008, 18
    Obviously this is not a cut, copy transpose issue. Also, while my sample only has 4 columns my actual data is much more extensive. Any help would be appreciated.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    UNpivoting data

    From John Walkenbach's website illustrating Joel Horowitz's technique:
    http://j-walk.com/ss/excel/usertips/tip068.htm

    With a range in this structure:
    Account____Dept_1________Dept_2
    Cash___________101___________102
    Inventory______201___________202
    Equip__________301___________302
    <Data><Pivot Table>
    Use: Multiple Consolidation Ranges_____Click [Next]
    Select: "I will create the page fields"_____Click [Next]
    Range: (Select your data)_____Click [Add]_____Click [Next]
    Click the [Layout] button
    ROW: Drag ROW off the diagram
    COLUMN: Drag COLUMN off the diagram
    DATA: Leave the VALUE field in this section
    Click the [OK] button
    Select a location for the Pivot Table_____Click [Finish]

    That will create a minimal Pivot Table containing only one cell with a value.

    Double-Click on that one value cell
    Excel will add a sheet to the workbook with the details of
    that cell in a database table format, like this:
    Row_________Column____Value
    Cash_______Dept_1_____101
    Cash ______Dept_2_____102
    Inventory__Dept_1_____201
    Inventory__Dept_2_____202
    Equip______Dept_1_____301
    Equip______Dept_2_____302
    Then change the column headings.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ 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. hiding partial rows &/or columns
    By toeragh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2007, 06:37 AM
  2. Shift Rows to Columns in Excelsheet
    By bhimacg in forum Excel General
    Replies: 3
    Last Post: 08-21-2007, 10:55 AM
  3. Hide or erase unused rows and columns
    By MaxAlbertsson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2007, 07:52 AM
  4. Delete hidden rows & columns
    By tommy_gtr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-04-2007, 06:12 AM
  5. Replies: 4
    Last Post: 04-26-2007, 02:13 AM

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