Converting Rows Into Multiple Rows - Large Data Set

[nisiwi]

With some assistance I managed to put together the necessary formulas to translate a dataset that needed each row splitting into multiple rows.
The original thread can be found here;

http://www.excelforum.com/excel-form...s-failing.html

This approach is fairly resource intensive and my Laptop fails to generate results after a certain point and with the full data set it just fails, so I was advised to look at VBA, however its an area that I'm pretty weak on unfortunately.

Can anybody point me in the right direction ?

ROW TO MULTI ROW.xlsx

Thanks,

Nic

[AB33]

It is not clear which columns you want to transpose. I followed your split sheet output.
There is a button on sheet data and the out put is on new sheet created by the code itself.

[nisiwi]

Excellent AB33, this is the basic result I'm looking for.

I'd also like to be able to add all the other fields, and the only complexity is the contact name and title.

I've attached an example of the headings in the original data source (TAB: Original Format) and what I'm trying to achieve in the output (TAB: New Format)

Basically all fields in blue and green need to be repeated for each set of Contact and Title in orange.

Below is a simple example, the attached file shows the actual fields;

Account Other Contact1 Title1 Contact2 Title2
ACCNA STU Fred Boss Joe Director

Account Other Contact Title
ACCNA STU Fred Boss
ACCNA STU Joe Director

DATA TRANSLATION EXAMPLE.xlsx

[AB33]

nisiwi,
The output sheet is blank. Could you please add raw data in input sheet and few lines showing your desired result?

[nisiwi]

Here's an update to that file AB33.

[AB33]

nisiwi,,
There is button on sheet reform column S. The output is on sheet reform.
The code is very sensitive to the layout of the data. If the lay out has changed, I need to change the code too.

[nisiwi]

Excellent AB33 - I will have to send you a beer (a few actually) donation!

Actually, my fault I did change the layout to make it simpler, however it looks like the attachment wasn't' updated correctly.

I've attached an update and will attempt to get my head around your code.

[AB33]

Amended code.

[nisiwi]

Great, thanks AB33. Actually I had a go myself using your code as a starting point and come up with the same result :-)

How would I ensure that columns K & L retain the prefix zero's ?

How can I send you a small donation as thanks ?