Forum Discussion
Combining 2 rows into 1 row and deleting a 3rd row
What I am looking to achieve is to join row 2 onto the end of row 1 and delete row 3, then duplicate this through the worksheet.
The data format does not change throughout
Any ideas on the best way to resolve this into this desired view
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
7 Replies
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:
Does this do what you want?
=LET(c, TOCOL(A1:D1000), f, FILTER(c, c<>0), WRAPROWS(f, 6))
Thank you, this returns what I am trying to do.
Is this a one-off or do you have to do this on a regular basis? Can you perhaps provide an Excel file with some dummy data, but in the current format?
