I have an issue with my data. My questionnaire is grouped into subsets. when i download it comes in different sheets this is not easy for analysing, how do i make themappear in one ? please help me out… Regards
You generally see 2 or maybe more sheets in your downloaded xls dataset when you have repeat group questions within your survey questionnaire.
Please note that the first sheet that is generally seen when opening your xls dataset is the main dataset. This dataset contains a variable named _index. If you go to the next sheet (which should be your first repeat group sheet) you should see a variable named _parent_index.
Theoretically, the number of cases in your first sheet (default sheet) is always less then or sometimes equal to the next sheet (that contains the repeat group response). The two sheets can be merged based on your analysis requirement. Please note that the cases from _parent_index should always be matched and merged from those with the _index or vise-versa (i.e. as per your analysis requirement).
You could merge the data from these two sheets using lookup command (if you are using MS Excel) or maybe you could also use some third party software such as SPSS, stata, R, SAS and so on.
Thanks for your help. Please, how to use lookup for merging it
MS Excel also offers data merging and analysis using ‘Power Pivot’ tool built into it. Each sheet serves as a datasheet which can be linked using the _index and _parent_index in a one-to-many relationship.
You will find many tutorials on how to use ‘lookup’ or ‘Power Pivot’ tool on YouTube.
There is another way which was tried to merge two sheets of data is through KUTOOLS for Excel with Table merge option. It worked great for me and then doing sorting
Welcome to the community! Thank you for letting the entire community know on
KUTOOLS for Excel that seems handy to merge dataset. Your valuable response should help a lot of community users who are having a hard time merging dataset at the moment.
Expecting the same in the upcoming days as well.
Have a great day!
Thanks so much for mentioning the KUTOOLS for Excel. In addition, those looking at excel scripts may also be able to work with VBA which has a lot of online resources from different people such as this one
Simple merging sheets can be done through various ways.
Here the main sheet is to be merged with secondary sheets based on the parent index. For eg.
Family 1 has 3 children
Family 2 has none
Family 3 has 5 children
The details of corresponding information of children should come against each family
I am not so sure whether this was a query or a comment. I would however like to make a general comment:
- When you move parent information to the childrens’ shee, you are basically creating a long data format which means that the family information will be added to each child so if a family had 3 children, then your merge will create three family information onto the 3 children.
- When you move children information to parent sheet, you have to convert it to wide format where each row represents a family and then the children are added as indexed columns e.g. if you had a variable say childname, then for three children you will have to add childname1 childname2 childname3. this process will be repeated for all variables.
With the above 2 principles in place, you have to to convert your sheets appropriately to perform the merge.
sharing with you an example file
after merging tables through kutoolsPARENT AND CHILD SHEET MERGE.xlsx (12.2 KB)
Its not necessary that family data will be repeated with every child. Only one family data comes followed by the family members details and then the second family data…
Then you needed to do something else, you needed to convert the data from the children i.e. family members from long to wide format. In this case
The variables in the repeat group i.e. family members will be repeated for every different family member i.e.
The instructions for achieving the above are provided by this third party link
You can then add the main information to each row since you will be using the index