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:
- http://www.cpearson.com/excel/ArrayFormulas.aspx
- http://www.mrexcel.com/articles/CSE-array-formulas-excel.php
- http://www.ozgrid.com/Excel/arrays.htm
So, here’s the sample data to show you the technique (not necessarily sorted by Supplier, as is the case below)…
And here’s what we want to end up with in our 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.
Download “Complex Transpose” Complex Transpose.xlsx – Downloaded 1597 times – 10 KB
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
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
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
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.
Respected Sir,
Thank you very much for uploading the useful and advanced formula in Excel.
Regards,
Vishwanath.N