I have a problem. I spend hours each week typing data from Excel in one format in excel into another format in excel! My theory is that if data is in excel at no point should I have to do any manual data entry to manipulate data into my desired format.

The data is structured by columns:

Date / First / Last / St Address/ City / Zip / Organization / A / B / C /
1/1/07 John Doe 13 pine st L.A. 94011 MNNC Inc 0 1 0

It needs to be spread across two rows in a different format. I have tried column merges etc from the one spreadsheet to the other. The problem is that these formulas work great on one set of data but cant be dragged and dropped. Even when I set up formulas for 4 sets of data for drag and drop it does not continue down the data just repeats those 4 sets of data.

As there are several hundred to do each week this is a desirable function! Please help me. I have a pretty basic understanding of excel but would love to know if there is a program or simple formula that could do this for me!

It should end up looking like this:

1) 1/1/07 John Doe MNNC Inc 0 1 0
2) 13 pine st, L.A. 94011

A,C,D,E,F should be merged across 1 and 2 , B1 and B2 should be separate cells

Anyway please please please help me out, I have tried all reasonable methods and researched possible ways to do this but have had no success. Any formula ideas or program suggestions would be greatly appreciated. Thanks!

The best way to do this is with a macro. Macros in Excel are written in Visual Basic. I don't think you can create such a macro by recording, but you could try. If you don't know Visual Basic, and you don't have a friend who does, then you might need to hire someone to do it.


This article has been dead for over six months. Start a new discussion instead.