# 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.

## 6 thoughts on “Complex Transpose”

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?

• It depends what you want the result to look like. This is really about transposing 2 columns of data only.

5. Respected Sir,