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:
- 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)...
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.
February 17th, 2012 - 02:06
Respected Sir,
Thank you very much for uploading the useful and advanced formula in Excel.
Regards,
Vishwanath.N