Need Help Moving Data from several Uneven Columns into one Formatted Table

[alacey425]

I am working on a project for grad school and have a large list of companies and their largest shareholders. I need help moving the data into an organized table.

Some columns are longer than others, and some do not contain any data and display #N/A N/A. I have attached an excel workbook that includes 6 columns of the data (the entire data has 937 columns).

I added an example of what I hope the table will look like below that begins in cell A25. If the cell has #N/A N/A, the company is disregarded. If the company has large shareholders each shareholder is recorded in the table along with their company ticker in the column to the left of their name.


This task is beyond my understanding and it would take me a long time to copy and paste the information. Any help is appreciated!

[oeldere]

Run the macro below on a dummy of your data.

After that you only need to sort the data.

NEW CODE added 11-02-2015: 21:46

Please Login or Register  to view this content.

[alacey425]

Thanks,

Is this a VBA formula? How do I run it?

[oeldere]

Yes it is VBA.

Push the button on the sheet output.

See the attached file.

[alacey425]

For some reason it does not copy all of the names. After Column C it only copies the first cell in Row 2 but skips the information in Rows 3,4,etc.

[oeldere]

I changed the posted code in #2.

See the new file with the amended code.

[alacey425]

Thanks,
When I plugged in my data I get an error and it says I need to debug and it is not giving all the names, just the first row in each column. Can you see what changes may have happened?

[oeldere]

The reason is that the code counts the values in column A.

What is the maximum number of names in a column?

[alacey425]

100 names to be safe.

is that why it says runtime error 1004?

[oeldere]

The N/A data also need to be saved (or they can be deleted) ?

[alacey425]

They can be deleted

[oeldere]

See the attached file.

I made an workaround added data in column A.

Other forummember can provide a "cleaner" solution.

After that on the worksheet Output:

I ran the macro delete text and delete #n/a n/a

I ran the macro sort

After that I made an pivot table.

There are 4.213 names in the sheet.

[alacey425]

I appreciate the help. You saved me an unbelievable amount of time.

[oeldere]

Thanks for the reply.

Glad I could help.

Since you are new on the forum, you can add rep(utationpoints) to the one who helped you by clicking on the star on the left side.
I noticed you already clicked the star, thanks for that.

[alacey425]

I see there are several macros now.. Should I just click the button you created or do I need to open the macro window and run through them? When I input different data into sheet 1 for another state it did not delete the columns or create the pivot table.

[oeldere]

The pivot table is added manualy.

The button only acivates the macro CONVERTROWSTOCOL_Oeldere_revisted_new()

Should I just click the button you created or do I need to open the macro window and run through them?

Yes you have to activatie them yourself.

Please Login or Register  to view this content.