Merging repeated groups to main sheet?

User Support Data Management
,
1

Hello,
I used the repeat group feature in a recent form to collect information. However, when I extract the data the sheet will is divided from the main sheet and three other separate sheets that have a column referencing to the parent index. Is there a way I can merge the three sheets in the main sheet? Thank you in advance.

2

Hi @bl_jo_datamgmt,

Welcome to the community! KoBoToolbox at the moment does not support the feature of merging dataset. However, you could merge your dataset using a third party software as outlined in the post discussed previiously:

Have a great day!

3

Thanks. Are you aware of another third party software? I do not have access to SPSS at the moment.

4

I’m more interested in being guided through the process if possible or if there is an available tutorial that reviews how to merge these types of datasets from KoBo.

5

Hi @bl_jo_datamgmt,

Any data analysis software should be able to merge the data. Tryout STATA, SAS etc.

Have a great day!

6

Yes, understood but would need something that is open-sourced.

7

Hi @bl_jo_datamgmt,

Haven’t tried out but you could also use MS Excel’s VLOOKUP/HLOOKUP command to merge a dataset.

Have a great day!

8

Thank you! If anyone else has any other solutions I would be happy to know more. Not sure how well the lookup feature is in this instance when each sheet has data with multiple datasets.

9

Hi @bl_jo_datamgmt
If you need an open-source software that can do this, then you should be able to do this within any open-source software you are familiar with. The tenet behind this is not the software but the logic around how to execute. I would like to sum the logic as follows:

  1. The main sheet (parent) has all the variables/questions/columns that are not repeated
  2. The subsequent sheets have variables within the group that are repeated with a sheet for each repeated group.
  3. The sheet in 1 and sheet/s in 2 are linked by the index or parent ID.
  4. Using the sheet/s in 2 above, then it means the data in sheet in 1 above would be repeated as much as the repeats found in sheet/s in 2.
  5. When using any software, find a way to plot the sheet 1 data into sheet/s 2 using the index or parent/ID.

Unfortunately, this operation is very form-specific and as such, I would request you custom it for your form. PS: if you have a form that you do this on a routine basis, then the use of macros in excel will be able to perfectly sort you. If you think you want to take this route for your form and you need any more support, I suggest you try the jobs section of the community forum and see if there is a user who would be willing to pick this task up and custom it for you.

Stephane

1 Like
10

@bl_jo_datamgmt, you should be able to merge repeat group data with the main data with Excel as outlined in the support article:

11

Hi,
Today I faced the same problem, I read the answers here, but I tried my own solution of using Vlookup formula to link the repeated group data to the parent sheet by Vlookup the index.
It worked out successfully :cowboy_hat_face:

1 Like
12

Welcome to the community, @mohamed_bashier! Thank you also for updating the post and letting the community know about Vlookup. We also have a support article that should merge the data through power query (which should be much easier):