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.

p.s. Do share any alternate versions you can think of to solve this.

Download the FINAL Workbook Below…