+ Reply to Thread
Results 1 to 6 of 6

Excel - 2016 - raw data header required in vertical column for pivots

  1. 02-21-2018, 02:38 AM #1
    russdmc
    russdmc is offline
    Registered User
    Join Date
    01-24-2013
    Location
    sydney
    MS-Off Ver
    Excel 2007
    Posts
    2

    Excel - 2016 - raw data header required in vertical column for pivots

    HI,

    I am trying to represent some data headers in a row with quantities in a vertical or column manner for pivoting.

    Obviously there are more columns and rows than in this example.

    I cannot just transpose as the data needs to be reflected against each line item in the order.

    It is difficult to describe so I have tried to attach/insert this example.

    TIF

    Excel Problem..JPG
    Reply With QuoteRegister To Reply
  2. 02-21-2018, 02:52 AM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Excel - 2016 - raw data header required in vertical column for pivots

    Hi, russdmc, welcome to Excel Forum! Please upload a sample workbook so we can test our solutions.

    To attach a workbook:
    Click Edit Post (or just start a new reply.)
    Click Go Advanced
    Scroll down to Manage Attachments and click.
    Now click Browse, find your file, then click Upload. Simple!
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
    Reply With QuoteRegister To Reply
  3. 02-21-2018, 03:57 AM #3
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Excel - 2016 - raw data header required in vertical column for pivots

    OK, I reconstructed one from your images (15 minutes I'll never get back ). The various formulas use these Named Ranges:
    Order = H3:H10
    Date = I3:I10
    PartID = J2:T2
    Qty. = J3:T10

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    2
    		 Order Date Part Qty. Order Date aa bb cc dd ee ff gg hh ii jj kk
    3
    10
    1-Jan
    		 bb
    3
    10
    1-Jan
    3
    1
    2
    4
    10
    1-Jan
    		 gg
    1
    11
    1-Jan
    1
    1
    4
    1
    5
    10
    1-Jan
    		 kk
    2
    12
    2-Jan
    1
    6
    11
    1-Jan
    		 aa
    1
    13
    3-Jan
    5
    5
    5
    7
    11
    1-Jan
    		 dd
    1
    14
    6-Jan
    1
    8
    11
    1-Jan
    		 jj
    4
    15
    7-Jan
    3
    2
    1
    9
    11
    1-Jan
    		 kk
    1
    16
    10-Jan
    1
    1
    1
    1
    1
    1
    1
    10
    12
    2-Jan
    		 aa
    1
    17
    15-Jan
    2
    6
    4
    2
    11
    13
    3-Jan
    		 dd
    5
    12
    13
    3-Jan
    		 hh
    5
    13
    13
    3-Jan
    		 kk
    5


    This array formula in A3: 
    Please Login or Register  to view this content.
    Standard formula in B3: 
    Please Login or Register  to view this content.
    This array formula in C3: 
    Please Login or Register  to view this content.
    ...and this standard formula in D3 (All copied down): 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by leelnich; 02-21-2018 at 07:10 AM.
    Reply With QuoteRegister To Reply
  4. 03-07-2018, 08:58 PM #4
    russdmc
    russdmc is offline
    Registered User
    Join Date
    01-24-2013
    Location
    sydney
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Excel - 2016 - raw data header required in vertical column for pivots

    Thanks for this...... Apologies for not attaching...... it long time follower first time user....May you be showered in good will and fortune
    Reply With QuoteRegister To Reply
  5. 03-07-2018, 09:38 PM #5
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Excel - 2016 - raw data header required in vertical column for pivots

    You're most welcome! If concluded, please mark your thread as SOLVED (Thread Tools above post #1). And since you're new...
    Clicking the Add Reputation star below helpful posts is a nice way to show appreciation to those who contributed. Regards – Lee
    Reply With QuoteRegister To Reply
  6. 03-07-2018, 09:39 PM #6
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Excel - 2016 - raw data header required in vertical column for pivots

    if you've Ex2016 you can do that with PowerQuery (Get&transform)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. 04-12-2016 date in excel changes to April 12th 2016 when in fact it should be Dec 4th 2016
    By raj4rev in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-18-2017, 08:27 AM
  2. How to create timeline project with vertical today marker (2010, 2013, 2016 etc...)
    By sandy666 in forum Tips and Tutorials
    Replies: 7
    Last Post: 08-12-2017, 01:49 AM
  3. Adding vertical lines to line chart in Excel 2016?
    By King Mustard in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-07-2017, 02:36 AM
  4. In Excel Cube pivots, How to search required data using VBA.
    By prabhavaram2048 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2016, 09:31 AM
  5. Index/Match with Header and Vertical data
    By asmith9947 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-13-2014, 10:56 AM
  6. Data will pair with vertical header, but not horizontal header
    By ddr4lyfe in forum Excel General
    Replies: 5
    Last Post: 08-25-2013, 02:59 PM
  7. Excel - turn a page number in a header from horizontal to vertical
    By pbrookstx in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-03-2005, 08:05 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1