You’ll learn a few methods to replace the TRANSPOSE function
In Part 3, I show you how you can use the INDIRECT, ROW, ADDRESS and later INDEX functions to avoid the use of TRANSPOSE which requires CSE
Make sure you watch PART 1 and PART 2 before you take a peek at Part 3.
There are two tricky parts here: one is that my check list includes multiple items and two, the unique count is across rows instead of columns.
In Part 2, I used MMULT (Matrix Multiplication) to count unique items across rows.
This became my favorite solution. I then added the TRANSPOSE formula to it to be able to expand my columns automatically.
Problem with TRANSPOSE is that it requires Control Shift Enter (CSE). I’d rather have a formula that doesn’t require CSE. After some brainstorming, I decided to use the ROW function. The challenge was to get my rows to depend on my columns. I ended up with three different formulas that replaced the TRANSPOSE function. These were the function combinations I used:
- INDIRECT & ROW
- INDIRECT & ROW & ADDRESS
- ROW & INDEX
The original purpose of this video was to show you how the INDIRECT function can be used here. Which I did, but my favorite formula ultimately became the one that doesn’t include INDIRECT. It’s the one with ROW and INDEX – where INDEX is used to return a cell reference instead of a cell value (Isn’t the INDEX function amazing?!)
Sounds complicated? Some things are not meant to be described. The video will help.