vba for rearranging the data

Hi All,

I want to rearranging the data in year wise for each company. My data (Sheet 1) is in this order.

Year Company A Company B

1996 Data 1 Data 1
.......
2006 Data 11 Data 2

I wan to rearrange the data (Sheet 2) in to the following order.

Company A 1996 Data 1
........
2006 Data 11

Company B 1996 Data 1
.........
2006 Data 11

Is there any possibility for vba for this soloution. If i will increase the no of companies, whether the vba will work or not.

I have attached a file for the same

Waiting for ur Suggestions,

Which sheet is Before and which sheet is After?

Hi Jb,

Sheet 1 is before and Sheet 2 is after.

Regards,

Try this, start the macro with the raw data sheet active, it will create an Output sheet for you.

Please Login or Register  to view this content.

Hi Jb,

Thanks a lot. I have a doubt that If I am going to increase the no of companies whether the macro will automatically takes care of that in arranging the columns or not ?

Thanking you.

Hi,

It seems the macro is not rearranging the data from the sheet 1. I want the output file should rearrange the data like sheet 2, where i have done for two companies.

Hope i am clear this time.

Regards,

Here's your own sheet back with the macro in it. I've made no changes to your sheet. Run it, it makes the OutPut sheet just fine.

here's another one:
Adjusted closing price(bvj).xls

Thanks JB,

Your Macro has saved a lot of time of mine.

with sincere regards,

Hi JB,

I am going to ask ur suggestion regarding one small problem. I want to add data on some more variable from Sheet 2. As my no of companies is not necessary equal all the time. But I want to add more variables from the sheet 2. Which formula i should use to pull the data of only those companies exists on output sheet and "Sheet 2" as well.

I have attached the file for the reference.

Thanks and Regards,
Upananda

Please start a new thread rather than tacking onto already solved ones. Include links to prior threads only when it seems it would be relevant, which it probably isn't in this case.