Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

Combine 2 rows into 1 row

Copper Contributor

This is probably very basic, but I can't for the life of me figure out how to accomplish this.

 

I have a spreadsheet where one row contains the information for an invoice and the next row contains the payment information for the row above.  I would rather have all that information on a single row.  It's an extremely long spreadsheet.

 

Seems simple enough.  But I can't figure it out.

 

TIA

4 Replies
I guess it is not simple.
If possible,share your file and expected result.

Hi @bbretton61 

 

It's not very basic. Could you post a few representative examples (or better share your file) of what you have & what you expect + confirm you run Excel >/= 2016 on Windows or Excel 365 on Mac up to date?

 

With consistent data structure & clean data we can easily go FROM blue TO green with Get & Transform aka Power Query:

Sample.png

best response confirmed by Peter Bartholomew (Silver Contributor)
Solution

@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

 

 ABCD

1

INV00111/01/2023John Smith2,500.00
2Visa2,500.00  
3    

4

INV00211/02/2023Jane Doe1,500.00
5Debit1,500.00  
6    

 

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

 

 ABCD

1

INV00111/01/2023John Smith2,500.00
2Visa1,250.00MasterCard1,250.00
3    

4

INV00211/02/2023Jane Doe1,500.00
5Debit1,500.00  
6    

 

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.

@djclements 

 

That's perfect!!!  It was consistent so super easy with using your answer.

 

Thank you!

1 best response

Accepted Solutions
best response confirmed by Peter Bartholomew (Silver Contributor)
Solution

@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

 

 ABCD

1

INV00111/01/2023John Smith2,500.00
2Visa2,500.00  
3    

4

INV00211/02/2023Jane Doe1,500.00
5Debit1,500.00  
6    

 

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

 

 ABCD

1

INV00111/01/2023John Smith2,500.00
2Visa1,250.00MasterCard1,250.00
3    

4

INV00211/02/2023Jane Doe1,500.00
5Debit1,500.00  
6    

 

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.

View solution in original post