+ Reply to Thread
Results 1 to 4 of 4

Transposing data from vertical to horizontal (INDEX/MATCH)

  1. #1
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2013
    Posts
    210

    Transposing data from vertical to horizontal (INDEX/MATCH)

    Hi all,

    On the attached spreadsheet, I need to transpose the Total Shift time from the Data tab onto the Summary tab.

    Hopefully that bit is pretty simple?

    However, the raw data may not always be in alphabetical, sequential order so I need the formula to be flexible enough to look through the entire data sample and figure out the correct result wherever the Driver name appears on the list.

    I assume it's somehow Index/Match related but I'm stumped.

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Transposing data from vertical to horizontal (INDEX/MATCH)

    Your days of the week on the summary sheet are a little askew.

    Try this is D4. It's an array formula, you must enter it by using Control + Shift + Enter, not just enter. You can then copy it across and down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Transposing data from vertical to horizontal (INDEX/MATCH)

    Try this regular formula. In D4 and pull formula across and down.

    You will get different results if you use D1:J1 range or D2:J2 so you need to choose which one to use.

    for D1:J1

    =SUMPRODUCT((data!$C$2:$C$16=D$1)*(data!$A$2:$A$16=$A4)*(data!$D$2:$D$16))

    for D2:J2

    =SUMPRODUCT((data!$B$2:$B$16=D$2)*(data!$A$2:$A$16=$A4)*(data!$D$2:$D$16))
    Last edited by AlKey; 02-17-2015 at 01:58 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2013
    Posts
    210

    Re: Transposing data from vertical to horizontal (INDEX/MATCH)

    Fantastic - I've gone for the array formula.

    Your help is much appreciated.

+ 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. Transposing repeating vertical data to horizontal data but retaining unique values only
    By mgcarino14 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2014, 12:57 AM
  2. Index Match with Horizontal and Vertical Matches
    By KMCurtis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2014, 04:18 PM
  3. Transposing Data from Horizontal to Vertical
    By OTexcel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-16-2012, 08:28 PM
  4. Transposing vertical data sets to horizontal positions
    By Ricker090 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2011, 01:40 PM
  5. Replies: 5
    Last Post: 04-23-2010, 08:01 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