Transposing Vertical Data to Horizontal using macros

[lesfell]

Hello!

I have been working on this file but I can't seem to get the right way to capture the data from one sheet to another. I need to capture some cell values from Sheet 1 and Sheet 2 (vertical) and transpose them to Sheet 3 (horizontal). Can anyone help me create a macros for this? I'd appreciate any help or tips. Thank you in advance!

Note: I have attached the sample file and have indicated instructions on Sheet 3

 

[vletm]

lesfell
Something like this sample?

 

[lesfell]

lesfell
Something like this sample?

Hello vletm!

Yes, sheet 3 should look like in what you attached. However, I need help in capturing all of those data using macros wherein I can do so using a button. Thanks for you reply.

 

[vletm]

lesfell
...hmm?
You can clean all those datas from Sheet3.
After that Select other sheet and
select back Sheet3.
Where do You need a button?
Sheet3 will refresh always without any ... button,
which wasn't with Your #1 reply.

 

[lesfell]

lesfell
...hmm?
You can clean all those datas from Sheet3.
After that Select other sheet and
select back Sheet3.
Where do You need a button?
Sheet3 will refresh always without any ... button,
which wasn't with Your #1 reply.

I don't think we are understanding each other here. I need help in fetching the data from sheet 1 and 2 to sheet 3 using macros vba wherein I can do so by just using a form control button and not manually copying data from one sheet to another. May I know how did you accomplish sheet 3 in the file sample you attached?

 

[vletm]

lesfell
Okay .... I'll try one more time.
Open this same file as above.
I cleaned those Sheet3-values away - okay?
Aren't those clear now?
Select Sheet1 - okay?
Select Sheet3 ...
Soon You'll get there values
... if marcos are enabled
( ... that file has macro, which works like below).
>> It's like a light in a fridge <<

 

[lesfell]

lesfell
Okay .... I'll try one more time.
Open this same file as above.
I cleaned those Sheet3-values away - okay?
Aren't those clear now?
Select Sheet1 - okay?
Select Sheet3 ...
Soon You'll get there values
... if marcos are enabled
( ... that file has macro, which works like below).
>> It's like a light in a fridge <<

Hello! Thank you it works now! However, the attached file is just a template and the issue now is if I change the cells in all sheets containing "Statement 1" and so on to the actual statements, the macros do not work anymore. Therefore, I won't be able to use the template you provided Is there anyway wherein if the statements are changed in the sheets, the macros would still work? Thanks

 

[vletm]

lesfell
You sample file should be as near as possible with Your real file - okay?
There are rules, how this works.
eg one rule is that all those 'Statement', can be eg 'Statement 1' or any same format - same text and space and number.
If You do modifications out of rules ... then it cannot work.

 

[lesfell]

lesfell
You sample file should be as near as possible with Your real file - okay?
There are rules, how this works.
eg one rule is that all those 'Statement', can be eg 'Statement 1' or any same format - same text and space and number.
If You do modifications out of rules ... then it cannot work.

Thank you so much! I will just find a workaround on that.

Additional queries/ request:

1. In the initial file provided, the range for the average for C81, D81... are from (C12:C29), (D12:D29)... only. So when I add values beyond C29 (cells highlighted in yellow in sheet 2), sheet 3 doesn't capture the additional values anymore. Can you please adjust the range so it can capture those beyond C29?

2. If there would be additional group data similar to sheet 2 and will be added as "Sheet 2 (2)", "Sheet 2 (3)", "Sheet 2 (4)", "Sheet 2 (5)", and will have a corresponding table in Sheet 3, can that be captured as well? or will the file be too large for that?

3. In sheet 3, can the items as indicated in Column H can be captured as well?

I am sorry for bothering you but I appreciate your assistance. Thank you so much for your help!

 

[vletm]

lesfell
#1 ... What should adjust?
You should show expected results or so.
#2 ... Those names of sheets should be like in this sample.
Your naming makes challenges for You ... soon.
Out-sheet is Your 'Sheet3'
Other sheets eg like this, but if You would like to rename those
... then You gotta modify manually Out-sheet's A-column names of sheets too.
#3 ... Can ... but this kind of detail should be know already with original sample.

 

[lesfell]

lesfell
#1 ... What should adjust?
You should show expected results or so.
#2 ... Those names of sheets should be like in this sample.
Your naming makes challenges for You ... soon.
Out-sheet is Your 'Sheet3'
Other sheets eg like this, but if You would like to rename those
... then You gotta modify manually Out-sheet's A-column names of sheets too.
#3 ... Can ... but this kind of detail should be know already with original sample.

Hello again!

1. To clarify, I want to add to the range of the statements in each sheet - e.g. Column C11 (Statement 1) in Sheet Data1 is now from C12:C111. Since I added rows, Out sheet does not capture the values anymore. Can this be adjusted please for all sheets? Please see attached file.

I am now good with question/queries #2 and #3 thank you

However, I have another request

Can you also help do the same macros to the 2nd file (Workbook_2) attached? Thank you so much! I am willing to donate again.

 

[vletm]

lesfell
#1 Each sheet have to have same term Frequency of ratings (FR) to enable this feature.

#2 Why those should be different? Left Workbook_2 and right side previous file.
Left has two 'extra rows' above 'Frequency...' if it compare to Your previous file - I didn't take care this.
It's possible ... but for You - for users - it would be more useful if same kind of layouts are same.

 

[vletm]

lesfell
I did 'minor modifications' to Your Workbook_2 > Workbook_3.
( You'll notice - what do 'minor modifications' ... mean? )
If You would use Table, then this could be one possible layout.
You could add Table's rows as many as needed ( no need to have 'spare-rows ).
... and so on.

 

[lesfell]

lesfell
#1 Each sheet have to have same term Frequency of ratings (FR) to enable this feature.

#2 Why those should be different? Left Workbook_2 and right side previous file.
Left has two 'extra rows' above 'Frequency...' if it compare to Your previous file - I didn't take care this.
It's possible ... but for You - for users - it would be more useful if same kind of layouts are same.
View attachment 80012

Hello!

#1 I think I am good with the file 'Sample Workbook_with additional sheets_2', however, can you please go check "Out" sheet again as I made the wrong references for the cells highlighted in yellow. The wrong values were copied.

#2 These are actually two different types of workbook so workbook_2 is really like that. I will reply to your follow-up post.

Thank you!

 

[lesfell]

lesfell
I did 'minor modifications' to Your Workbook_2 > Workbook_3.
( You'll notice - what do 'minor modifications' ... mean? )
If You would use Table, then this could be one possible layout.
You could add Table's rows as many as needed ( no need to have 'spare-rows ).
... and so on.

1. I checked workbook_3 but in my end some of the value are not showing up in my end.


2. However I just copied the format you did for workbook_3 to workbook_2 and maybe that can help with extracting the values from sheet CS to sheet MRCSAT. Kindly just double check the highlighted cells (in yellow) in sheet MRCSAT. I made a mistake from Statements 10-12, i initially numbered it 11-13 so the values in Statement 10 (B38) are from Statement 11. Thank you!!

 

[vletm]

lesfell
#1 Yes, some value haven't take care after You'd added more rows.
... You can have needed rows - I tested that with Data1-sheet.
>> check Workbook_1.xlsb.

#2 Where do You need those right side Elements averages from three statements?
General Mean Score could solve as with Your original file.

1) I've hidden zero-values
2) I did something for Workbook_2.xlsb

 

[lesfell]

lesfell
#1 Yes, some value haven't take care after You'd added more rows.
... You can have needed rows - I tested that with Data1-sheet.
>> check Workbook_1.xlsb.

#2 Where do You need those right side Elements averages from three statements?
General Mean Score could solve as with Your original file.

1) I've hidden zero-values
2) I did something for Workbook_2.xlsb

I have checked the files. I will reach out to you if I have further questions. It isn't much but I hope you got my donation. Thanks so much for your help again!

 

[lesfell]

lesfell
#1 Yes, some value haven't take care after You'd added more rows.
... You can have needed rows - I tested that with Data1-sheet.
>> check Workbook_1.xlsb.

#2 Where do You need those right side Elements averages from three statements?
General Mean Score could solve as with Your original file.

1) I've hidden zero-values
2) I did something for Workbook_2.xlsb

Hello, I am having troubles with the files. It isn't working on my end anymore. What could be the issue? Thanks

 

[vletm]

lesfell
Can You send those Your files for me?

 

[lesfell]

Hello!

I just want to ask a question regarding the files. In workbook 1 for example, why is the General Average in tab Data2, Data3 etc. different from the General Average Data2, Data3 etc in tab Out?

Thanks!

 

[vletm]

lesfell​

.... about 11 months later ...
If Your Data1-sheet has ... data

... and other Data-sheets do not have data

What would show in Out-sheet?

Or
What are You wondering?
What exact is different?
... should some thing be same?

 

[lesfell]

For example in Data 2 tab the general average is 3.40



But in the Out tab the general average is for Data 2 is 3.46

 

[vletm]

I don't have same values than Your above examples.
Neither in my file do not have Data 2 tab in my file.
About 11 Months ago all works .., Jul 17, 2022 You had some actions.
Do above happen in last 30 days?

 

[lesfell]

I have just downloaded the attached file from the one you attached in our July 17 conversation. The general average score in the Data2 tab and the one general average score in the Out Tab are different.

Is it possible for you to send me the file you have to double-check?

 

[vletm]

Did You double or more check it about 11 months ago?
... in those days, You did some unwanted actions.
... or what have You done with that file?

>> I checked Your file >>
#1 You have modified some Your formulas
- You should modify all Your formulas and there are many missing if those - be careful.
#2 My code calculates many of those values and shows those results in Out-sheet
... Data1-sheet has 9 elements and others have 10 elements
... for some reason - even You have skipped that there are always take care 9 elements results from all Data-sheet.
... Checking / testing / checking / testing at once is much better to do ... than after a long time later.
>>> now, it take cares always 10 elements per Data-sheet.

Is there any reason - why You skipped below? ( #16 reply )
#2 Where do You need those right side Elements averages from three statements?
General Mean Score could solve as with Your original file.
That file would be more smoother to use ... if I could remember details ... about 11 month ago.