How do I combine multiple rows of data into one row?

[sdorn]

I am trying to create a product SKU sales report with 6-years of product SKU sales data on a single row. Unfortunately the export from our database only exports 2 year periods per row. I have manually created header rows, and moved the data to the appropriate column. However I am left with three rows of information to work with. Is there a formula to combine the 3 rows into one row and to sum the data in the 6 columns? An example is below.

Col1, Col2, Col3, Col4, Col5, Col6, Col7
SKU, 2010, 2009, 2008, 2007, 2006, 2005
Item#1, 25, 22, , , , ,
Item#1, , , 20, 15, , ,
Item#1, , , , , 35, 40
The Product Sales data is in the appropriate column already.

I would like it to appear in one row as:
Item#1, 25, 22, 20, 15, 35, 40

Any suggestions?

 

[jbeaucaire]

'COLUMNS TO ROWS
Here's a macro for merging rows of data to one row matching for column A.

Columns to Rows - Part 3​

There's a sample workbook at the top of that page to help you test this, but the Part 3 macro is designed specifically to do this very thing. Give it a try.

 

[sdorn]

That worked great. Thank you for the Macro and Samples. Saved a tremendous amount of time!

 

[JaneR]

'COLUMNS TO ROWS
Here's a macro for merging rows of data to one row matching for column A.

Columns to Rows - Part 3​

There's a sample workbook at the top of that page to help you test this, but the Part 3 macro is designed specifically to do this very thing. Give it a try.

You saved me from a complete mental breakdown. Thank You!!!