Partially "transpose" rows with formula

I wrote this little subroutine which can do it for you:

Please Login or Register  to view this content.

And here's an example. Whee!

I came up with a way to transpose the left columns with nested LOOKUPS, but more than a hundred or so of them would bring your computer to its knees.

So I'd say the VB route is much safer. And faster too.

Originally Posted by Emma_B

transpose the last 3 columns according to the criteria

What criteria?

Here's a formula-based solution if you're interested.

This solution assumes that the blanks in your source range are "genuine" blanks (and not e.g. the "" as a result of formulas in those cells).

I'm putting the transposed results in columns J-O, from row 2 onwards, though obviously amend if you wish (though being careful to amend all references appropriately).

Case: cell J2, array formula**:

=IFERROR(INDEX($A$2:$A$6,MATCH(TRUE,MMULT(0+(ROW($A$2:$A$6)>=TRANSPOSE(ROW($A$2:$A$6))),SUBTOTAL(3,OFFSET($F$2:$H$2,ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6)),,,)))>=ROWS($1:1),0)),"")

Copy down until you start to get blanks for the results.

Field1: cell K2, non-array:

=IF($J2="","",INDEX($A$2:$H$6,MATCH($J2,$A$2:$A$6,0),MATCH(K$1,$A$1:$H$1,0)))

Copy down as required and also a further three columns to the right to give results for Field2, Field3 and Field(n) (I don't know what this last means: if you are intending to suggest that you could have an unknown number of column's worth of data, then I'm afraid it's not clear. That would in any case require a more complicated solution.)

Option: cell O2, non-array:

=IF($J2="","",INDEX(INDEX($F$2:$H$6,MATCH($J2,$A$2:$A$6,0),),,COUNTIF($J$2:$J2,$J2)))

Again, copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You need to repeat the headers in K1:N1 precisely as they appear in B1:E1, since they are being used by the formula to obtain the relevant data.

Regards

In reference to the message you sent me:

To adjust the code, you can simply add in an extra line at the beginning to delete the copy to area so it's fresh every time.

Range("A14:F999").ClearContents

Please Login or Register  to view this content.