how to transpose/move data from columns to rows

**snk2u** · 05-18-2013, 01:52 PM

Hello,

This is my first post on the excel forums and I decided to look for help after I googled but coulnd't find an answer to my problem.

So, I have a column with data on rows as follows below. I need to arrange the data below in such a way that I have on first column the company name, second column the contact person and so on depending on what data is found (tel, e-mail, website).

The data is on rows and I have separated each company/group of data with a row between them. There are in total aprox 200 companies/groups of data that I need to arrange as explained above.

I have tried with transpose but I have to manually do it 200 times. I have tried with an indirect formula but the companies/groups of data do no have same amount of info/rows e.g. some lack the phone number or other data.

Please let me know if there is a method to save the time and not arrange them manually.

Actinote
Contact: Toine Kets
Managing Director
Str. Tache Ionescu 3, Et. 5, Apt. 10
010351, Bucuresti, Sector 1
T: +40 728 960 403
E: [email protected]
I: www.actinote.ro

Advanced Label Technologies
Contact: Imre Bodo
Business Development Manager
Sat Lupeni 648
537165, Lupeni, Jud. Harghita
T: +40 266 221 792
F: +40 266 221 796
E: [email protected]
I: www.sentega.com/alt

Advocaat Kantoor
Alina Ioana Georgescu
Contact: Cristina van der Schaaf
Office Manager & Advisor
Str. Tudor Arghezi 26, Et. 2, Ap. 24
020946, Bucuresti, Sector 2
T: +40 730 882 288; 744 327 223
F: +40 21 313 5790
E: [email protected]; [email protected]
I: www.avocatag.ro

Agri-Business Development
Contact: Daniela Ruda
Sos. Nicolae Titulescu 51-59, Bl. 11B, Sc. A, Et. 9, Apt. 45
011133, Bucuresti, Sector 1
T: +40 21 310 8053
F: +40 21 310 8053
E: [email protected]
I: www.vantuyll.nl/vtm/

etc....

Thanks

**AB33** · 05-18-2013, 02:43 PM

It can be done by either formula or a macro code. If you could attach your sample, I will have a crack using a code. To attach a sample, go to advance then attachment.

**snk2u** · 05-18-2013, 03:15 PM

Hi AB33,

Ok, I hope attached it correct.

George

**FDibbins** · 05-18-2013, 03:24 PM

Sorry, I deleted that file, it looked like it contained sensitive info like peoples names, adresses and email adresses.

I would suggest sending a file without sinsitive data like that

**snk2u** · 05-18-2013, 03:46 PM

Sorry for that, I attach a dummy one with no sensitive data.

**FDibbins** · 05-18-2013, 08:03 PM

If your data "sets" were the same length, this would work...
=OFFSET($A$1,(ROW($A1)-1)*5+COLUMN(A1),0)
where *5 is how many rows per set.

But that wont work with variable length "sets"

(
Maybe some-one can take that and modify it for vaiable lengths

**AB33** · 05-19-2013, 06:09 AM

Try the attached. It is a code, not a formula. There is a button on column B. The outcome is on column C, but if you want to be on different sheet, I will change the code.

**snk2u** · 05-19-2013, 02:22 PM

Hi,

The macro works for bigger database as mine. The transpose puts some companies on the same row but I can clean that. Definitely a great improvement from what it was !

many thanks AB33 !!

George