How do I combine excel data from two rows into one row in a new workbook?

Posted on 2014-08-03
Last Modified: 2014-08-05
I have an excel table where each two consecutive rows are one "record" and I need to extract data from both rows and combine the data into one row in another workbook?  Can you help me please?

An example of the table is attached.

Thank you!
Question by:mcarsonsr
    LVL 20

    Accepted Solution

    See Sheet2, using Index.
    Formulas for column A to J (first row) are identical
    A2: =IF(LEN(INDEX(Sheet1!$1:$65536,(ROW()-1)*2,COLUMN()))=0,"",INDEX(Sheet1!$1:$65536,(ROW()-1)*2,COLUMN()))
    Copy to the other columns B:J, row 2.
    Formulas for column K to V (second row) are also identical.
    K2: =IF(LEN(INDEX(Sheet1!$1:$65536,(ROW()-1)*2+1,COLUMN()))=0,"",INDEX(Sheet1!$1:$65536,(ROW()-1)*2+1,COLUMN()))
    Copy to the other columns L:V, row 2.
    Then copy the entire columns in row 2 down, enough to get all rows in table, and delete the empty at the bottom.
    Move the sheet to an new workbook.
    LVL 44

    Expert Comment

    Your sample workbook does not show us how the data should look after the 'combination' process.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
    A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now