Forum Discussion
Combining 2 rows into 1 row and deleting a 3rd row
DIverkeith If the number of fields to be returned for each output row is always six (6) you can simply use WRAPROWS/TOROW as follows:
=WRAPROWS(TOROW(A1:D11, 1), 6)Setting the [ignore] parameter to 1 for TOROW will ignore the blank cells automatically.
WRAPROWS/TOROW Results
DIverkeith If the number of fields to be returned for each output row is always six (6) you can simply use WRAPROWS/TOROW as follows:
=WRAPROWS(TOROW(A1:D11, 1), 6)
Setting the [ignore] parameter to 1 for TOROW will ignore the blank cells automatically.
WRAPROWS/TOROW Results
Thank you ...... That works perfectly!
Curiously how does the formulae work ... if you don't mind explaining that is
Regards
Keith
DIverkeith No worries. The TOROW function transforms or sends the range A1:D11 to a single row, reading left-to-right, top-to-bottom by default. To see what the result of TOROW looks like, try it on its own without setting any additional parameters: =TOROW(A1:D11). Then try it with the optional [ignore] parameter set to 1 - Ignore blanks: =TOROW(A1:D11, 1). Notice how all of the blank cells are removed from the results with the second formula.
The WRAPROWS function then takes the results of TOROW (a one-dimensional array) and converts it to a two-dimensional array by "wrapping" the values into separate rows. The length of each row is determined by the wrap_count parameter, which in this case is 6.
Hopefully that helps clarify things. If you would like to read up further, exceljet does a good job of explaining both functions with additional examples:
Thank you for the explanation, Understanding how it works makes it more useable.
appreciate your time and advice
