Pivot data

[corinereyes]

Hello,

Good day.

I have a sample survey data on sheet named "Database", data are collected from the sheet named "Survey Sheet".

My needed results are shown in sheet named “summary”

I have tried to use pivot table to show these results but it seems my database is not organize or not correctly set-up to show the results I wanted.

Any help or guidance will be greatly appreciated.

Thanks in advance.

[benishiryo]

hi Corine. do you need help for the VBA or how the data should be structured? the data source for the pivot needs to be 1 column for a group of categories. that means your questions should all be in a single column. your responses should be in another one. i've attached an eg. the rest of the data will repeat when it's the same questions. let me know if you need the codes too & i'll try to do up one.

[corinereyes]

Hi benishiryo,

Thank you for giving me an idea on how to structure my data, however, for each respondent there are 37 questions. Can you please help me on how to structure this using the correct method? or vba? I have my sheet named " Survey Sheet" which i encode all the answers from respondents.

I copy those answers in sheet named "Database" through vba, but it seems the structure is not right.

Again, Thanks in advance.

[benishiryo]

should be able to shorten the codes further, but i'm not really good in it. so here goes:

Please Login or Register  to view this content.

i merely added some codes to your existing one. you can replace the entire code for the "Private Sub CommandButton2_Click()". since 1 record takes up 37 rows now, you can change the formula in G14 to:
=COUNTA(Database!A2:A4997)/37

and something extra, you can actually use this formula for Y19 onwards:
=IFERROR(INDEX($C$18:$G$18,MATCH(1,C19:G19,0)),"x")

[corinereyes]

Hello benishiryo ,

Thank you very much for your help. I was able to re-structure my data and show the results i needed.

I just need to amend my codings as per your recommendation.

I attached a sample showing the correct results.