special transpose

**cata.maican** · 05-12-2011, 01:10 AM

Hi,

I'm pretty new to excel and i've googled my question but i couldn't find an answer. Please help me in a special transpose question.

I have the following data set:
year article name1 name2 name3 name4 name5....
2005 a1 n1 n2 n3 n4 n5
2005 a2 n1 n6 n9
2005 a3 n2 n20 n30

(a data set composed of publication year, article name and author names). I would like this data transposed in a normalized (relational) way: year, article, name, so the example above becomes:
year article name1
2005 a1 n1
2005 a1 n2
2005 a1 n3
2005 a1 n4
2005 a1 n5
2005 a2 n1
2005 a2 n6
2005 a2 n9
2005 a3 n2
2005 a3 n20
2005 a3 n30

Please help.

Thank you,
Catalin

**stanleydgromjr** · 05-12-2011, 07:09 AM

cata.maican,

Welcome to the Excel Forum.

Detach/open workbook ReorgData - cata_maican - EF775715 - SDG10.xlsm and run macro ReorgData.

If you want to use the macro on another workbook:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

**stanleydgromjr** · 05-12-2011, 08:09 AM

cata.maican,

And, the above macro will adjust for a varying number of columns of name titles in row 1.

**cata.maican** · 05-14-2011, 01:48 AM

Thank you very much for your help. It works like a charm!

Catalin

**haq_enam** · 05-14-2011, 02:33 AM

I have similiar problem but bit different ...My daya is something like that

Clm1 Clm2

Kamal Kabul
Kalam Oasim
James Kamal
James Kabul
James Devid

I want to see

Clm1 Clm2 Clm3 Clm4
Kamal Kabul Oasim
James Kamal Kabul Devid

Is there any macro for that...

**stanleydgromjr** · 05-14-2011, 08:19 AM

haq_enam,

Welcome to the Excel Forum.

You should really create your own NEW post, with the information you have already supplied.

When you have completed creating your own NEW post, you could send me a Private Message, with a link to your NEW post, and will assist you.

So that we get it right the first time you should also attach a workbook with before after after worksheets.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.