transposing many data in columns from rows (different sheets)

[moniquem]

Hi! how about if I want to transpose many columns of data into rows and in different sheets?

here is an example. But in reality, it is composed of different sheets (1 sheet per variable).

I want the format to be like this:

DATE (month/day/yr) Variable 1 Variable 2 Variable 3

jan 1, 1999 3 4 5

up to

jan 31, 1999 6 7 8



The raw data contains many years. I wish to have the format like the above one. 1 year per sheet.

[arlu1201]

You need to explain a little more. Maybe show us a before and after sheet so we can understand what really you need.

[moniquem]

Hi Arlu! here is the before and after files.

Please note that the data I will be using is not limited to 5 branches and 4 years. That's why I really need help. Thanks!

[arlu1201]

Try this code - Have toiled for hours to get this working for you -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[moniquem]

Hi arlu! Thanks for the code! I can't believe this that was really cool and amazing! You really are a genius!
By the way I want to ask what will I change if the branches will have different names as the labels (B1;C1;D1;etc). For example I will not label it as branch A but as "max" branch B as "min" etc etc.

Thanks for your brilliance!

[moniquem]

It also doesnt work if the column with months Jan-Dec is replaced with 1-2-3-4-5-6-7-8-9-10-11-12..
And another problem I forgot to tell you that there maybe some missing data.. For example there is no data for a month. It turned out that the transforming is continuous even if there is no data so the data is misplaced during transformation.

[moniquem]

Hi! There's some data missing but I want it to be formated the right way (data corresponding to the right date). The code doesnt work. pls see attached. Please. Thanks so much!

[moniquem]

Btw, the date should be displayed even though they dont have corresponding data. Thanks! Im sorry if this causes much confusion> I hope you are able to comprehend my wishes? my apologies for causing difficulties.

[arlu1201]

Hi arlu! Thanks for the code! I can't believe this that was really cool and amazing! You really are a genius!
By the way I want to ask what will I change if the branches will have different names as the labels (B1;C1;D1;etc). For example I will not label it as branch A but as "max" branch B as "min" etc etc. Thanks for your brilliance!

Will the sheet names have the word "branch" in them? The columns will not get affected but the sheet names will cause an issue in the code.

It also doesnt work if the column with months Jan-Dec is replaced with 1-2-3-4-5-6-7-8-9-10-11-12..
And another problem I forgot to tell you that there maybe some missing data.. For example there is no data for a month. It turned out that the transforming is continuous even if there is no data so the data is misplaced during transformation.

Ok, i will need to re-work the code for this.

Hi! There's some data missing but I want it to be formated the right way (data corresponding to the right date). The code doesnt work. pls see attached. Please. Thanks so much!

Ok, yes i will need to check and re-work the code for this part.

Btw, the date should be displayed even though they dont have corresponding data. Thanks! Im sorry if this causes much confusion> I hope you are able to comprehend my wishes? my apologies for causing difficulties.

Ok, i will add this part into the code.

[moniquem]

Thanks for the help! The sheet names dont actually have "branch" in it. They are different parameters. Btw when can I check the new code? thanks a lot!

[arlu1201]

So is it ok if the output is given in a new workbook?

[moniquem]

Yes it is okay.

[moniquem]

Actually it is better if the output is in a new workbook.

[moniquem]

Any update pls?

[arlu1201]

Try this updated code - I am not able to put in the dates into the 1st column - let me know if its too much of a headache to do it manually. I will try and break my head to put in some logic for that -

Please Login or Register  to view this content.

[moniquem]

Hi! It is okay if the data is not put into the first column. I can drag it.

Thanks for the code but there are errrs:
1. There are always 3 spaces before the 1st day of march; another space before may; july; and october. So the data is offset by 7spaces. So data is misplaced.

2. If there is missing data for a month or for a couple of months the data seems to be misplaced (e.g. Ted data goes into the Barney column)

[arlu1201]

Ok, yeah it creates an issue because the number of days in each month is not the same.

I will need to check again how i can make the logic better. I may need to redo the code.

[moniquem]

Thanks again! Hope to hear from you again. Sorry if it's laborious.

[moniquem]

bump
any update?

[moniquem]

Hi! I've used the code given by arlu, and I realized I don't really need an upadated code.. I correct the error manually by careful observation since they are minamal. Thank you very much Arlu!

Do I need to close this thread now? I have a different set and format of data that I want to transform like we did here but I guess the approach will be different since we will not be dealing with rows. Can I post it here?

[arlu1201]

Did you use the 2nd code? Yes, you can mark this as solved.

Since the question is different, you need to post it in a new thread.

[moniquem]

Yes, I used the 2nd code. Thank you very much, Arlu! You're brilliant!

[arlu1201]

Am glad its solved.

[moniquem]

What modification does it need so it would not be just pasted but pasted LINKED? Need help again.

[moniquem]

bump.. any update?

[arlu1201]

Usually, once a thread is solved, i unsubscribe from it so i dont get any notifications. So please PM me if i dont reply to you.

What do you mean by linked? You want the formulae to be in there and not the values?

[moniquem]

Thanks for the reply, Arlu!
No, the formula must not be posted but it should be pasted link. For example, in sheet 2, cell A1, the content is Sheet1!B1. It means that in sheet2 A2, the content in sheet 1 B2 is reflected. So when I change the value in sheet 1, the value in sheet 2 will be automatically changed.

[arlu1201]

So you do not want the macro to populate the data for you but to populate the data via formula?

[moniquem]

No. I just want the data to be pasted TRANSPOSE and LINK. I dont know if it's confusing but I cant explain it very well.

[moniquem]

Here is what I mean with paste link. But I dont know it it is possible to transpose while linking simultaneously.

[arlu1201]

Your sample file in post 30 shows the data in sheet2 not transposed but linked. Do you want it only linked?

[moniquem]

no i want it to be transposed and linked but i cant do it.

[arlu1201]

Any reason why you want it that way? Its going to make your work all the more complicated.

If you want to do it so as to include any changes done to the main file, then you can have the macro run each time a change is done.