Hi team,
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
Hi @mmarwa,
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.
1 Like
Thanks for your help. Please, how to use lookup for merging it
1 Like
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.
2 Likes
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
1 Like
Hi @jivansj,
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!
Hi @jivansj,
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
Stephane
2 Likes
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 Data
Family 1
Family 2
Family 3
Family Details
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
Hi @jivansj
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.
Stephane
1 Like
sharing with you an example file
after merging tables through kutoolsPARENT AND CHILD SHEET MERGE.xlsx (12.2 KB)
1 Like
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…
1 Like
Hi,
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
Stephane
1 Like
Hi @jivansj
Do you have any video tutorial about how to create this file? Please help me to create this type of dataset.
@tanzilhuda, if you have not gone through our new support article on merging datasets:
It should help you solve your issue!
1 Like
Hi @Kal_Lam
Thanks, thanks, thanks a lot. It is absolutely what I badly need. Really you are awesome.
1 Like
