Merging multiple Excel files, generated when exporting data from ODK

1. What is the problem? Be very detailed.
I have a data entry form which requires recording the ID and gender of all eligible household participants before collecting actual survey data. This means that, for instance, I would have to enter ID "A", then female, and have the option to add an additional entry if there are more eligible participants. When the total number of eligible participants have been recorded, then I can move on to the actual survey questions (which are actually observations to record, and every time a participant performs a relevant action, you enter their ID and answer the questions linked to what you observed).

The issue that I have is that, when I download the submitted data, ODK then produces three different Excel sheets with, for instance, one sheet with participants ID and gender, then another one with the actual survey questions, and so on. As I anticipate this will make it tedious to then merge the data collected to the relevant participant, I was wondering if there was a way to resolve this issue?

2. What app or server are you using and on what device and operating system? Include version numbers.
I am using ODK Central. I am not sure of the version I am using. However, this is what I found on ODK in the version section (I apologise, this is my first time using ODK):
client (v1.1.2)
server (v1.1.1)

I am working on the remote interface of my office computer, and my office computer is a PC. I do not know what the operating system is, but as it is a University, I am assuming this must be Windows 10 latest version.

3. What you have you tried to fix the problem?
I did not try to fix the problem, as this is my very first time using ODK.

4. What steps can we take to reproduce the problem?
To reproduce the issue, you would need to create a data collection form where you would have the option of adding multiple participants, and each time you finish adding basic information for a participant (say ID and gender), have a prompt asking if you have another participant to add. You should then add, say, two, three more participants, and then when prompted again to add more participants, chose no. The form you would have created would then take you to the actual observation questions (for instance: You would choose if the person whose ID you entered was cooking, cleaning, feeding a child, etc; then choose if that person used gloves, or x cleaning products, or ex food, etc.) Thus, every time you observe a participant performing a relevant action, your form would make you enter their ID, and then answer questions about the actions you observed. I hope this is clear.

5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.

Hi @Akissi, there is currently no way around the fact that you'll have to join the tables using downstream tools like Power BI or R. We're considering adding a way to flatten these kinds of data sets in Central, but nothing to announce at the moment.

One trick you can consider is to "forward" some of the data that is at the household table into participant table. So, for example, if you had a household_id question, in the repeat for the participant, you could add a calculate question called forward_household_id with a calculation of ${household_id}.

This technique duplicates the data in the household table into the participant table, so I'd only do it for a handful of questions.

Here's a worked example in R:

You can see the submission ID / ID fields you need to join by. In Excel that could be done with a vlookup.