ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

13Jul/116

Complex Transpose

I’ve called this tip ‘Complex Transpose’, which hopefully describes what I’m about to show you. Essentially, it’s about transposing a two column data set of related items into a multi-row and column data set.

The key technique employed utilises Array formulas, which I won’t go into detail about, mainly because plenty before me have (see links below). The quick 'how-to' is that you need to hold down the CTRL & SHIFT keys when pressing ENTER to commit the formula, instead of just hitting ENTER. Their other common name is a ‘CSE formula’ (Control, Shift, Enter). When committed, the formula is surrounded by curly {brackets}.

There are plenty of useful guides out there for Array formulas, including:

So, here’s the sample data to show you the technique (not necessarily sorted by Supplier, as is the case below)...

Sample data

And here’s what we want to end up with in our complex transpose result...

Complex Transpose result

In order to obtain this result we need to throw the following functions into the mixing bowl...

  • IFERROR – for cleaning up when we run out of product IDs.
  • INDEX – for returning the Product ID from a specified row.
  • SMALL – for returning the k-th smallest number in an array.
  • ROW – for returning the row number of each Supplier.
  • COLUMN – for calculating the column number in the output table.

Throwing them into cell E2 - beating with a whisk while adding water - gives the following scary combination...

={IFERROR(INDEX($B$2:$B$11,SMALL(IF($A$2:$A$11=$D2,ROW($A$2:$A$11)-1),COLUMN()-COLUMN($D2))),"")}

Time to dissect and see what we have!

This part gets the row number we're after in the Supplier Range
SMALL(IF(SupplierRange=SupplierName,ROW(SupplierRange)-1),COLUMN()-COLUMN(SupplierName))

The row number is then used to get the Product ID for that row
INDEX(ProductRange, SMALL(...))

We then trap any errors when a Product ID doesn't exist for the given column of the results
IFERROR(INDEX(...),"")

NB: The IF function is the array formula part, which requires CSE.

Simple right? Well, probably not at first glance. I find the best way to learn is to get your hands dirty, so download the sample file below and get learning!

I’ve included versions for Excel 2003 and 2007, because the IFERROR function didn’t exist in Excel 2003. Therefore the 2003 version utilises the IF(ISERROR()) workaround. See my last post about replacing these if you’re interested.

Complex Transpose
Complex Transpose
Complex Transpose.xlsx
Version: Excel 2007
10.2 KiB
1434 Downloads
Details...

Complex Transpose
Complex Transpose
Complex Transpose.xls
Version: Excel 2003
23.5 KiB
908 Downloads
Details...

Comments (6) Trackbacks (0)
  1. Thanks for the tip!

    What about if we wanted to go the other way? Meaning we have something that looks like the result, but we want to go back the other way.

    Thank you,

    Matthew

  2. Hi,

    Thank you for posting this. Despite playing around with this I am still having trouble. Could add a few more steps to make it clearer please?

    Thank you!
    Erika

  3. Thank you for posting, (I think I am almost there) however, I am still struggling to get an output. I put this exact formula in:

    = IFERROR(INDEX($B$2:$B$11,SMALL(IF($A$2:$A$11=$D2,ROW($A$2:$A$11)-1),COLUMN()-COLUMN($D2))),””)

    and hit control shift enter and nothing happens except for the coloured boxes. Any suggestions on how to get an output?
    Thank you

  4. This formula is very helpful. If I wanted to add a column for product prices how would you alter this formula to transpose all of that information?

  5. Respected Sir,

    Thank you very much for uploading the useful and advanced formula in Excel.

    Regards,
    Vishwanath.N


Leave a comment

No trackbacks yet.