Combine 2 rows into 1 row

bbretton61 There is a rather simple solution that might be possible, using the TOROW and WRAPROWS functions, but it all depends on the consistency of your data...

1) Does every invoice row always have an accompanying payment row directly below it?

2) Is there ever more than one payment row per invoice?

3) Are there any blank rows that separate each invoice? If so, is the number of blank rows per invoice consistent for the entire data range?

Assuming the total number of rows per invoice is consistent throughout the entire spreadsheet, the following two examples would work.

Example 1: Data contains a consistent number of invoice and payment details

In this example, since there are a consistent number of invoice details (4), as well as payment details (2), the following formula can be used to return all 6 items to one row for each invoice:

=WRAPROWS(TOROW(A1:D6, 1), 6)

The TOROW function takes all of the data from the selected range and moves it to a single row. The optional [ignore] argument in this case is set to 1 to ignore blanks. The wrap_count argument of WRAPROWS is set to 6 to return a total of 6 items per row.

Example 2: Data contains a variable number of invoice or payment details

In this example, since the total number of items varies for each invoice, the formula must be modified as follows:

=WRAPROWS(TOROW(A1:D6), 12)

In order to return a consistent number of items per invoice, the optional [ignore] argument could not be used in this case. Instead, the wrap_count was determined by multiplying the number of columns in the data range by the number of rows per invoice, including blank rows (4 x 3 = 12). If there were no blank rows in the above example, the wrap_count would have been 8.

Note: any blank cells from the original data range will be filled with zeros in the resulting array.