Excel data handling question

October 27, 2024 7:37 PM

I am trying to separate data from a Jotform web form outputting to an excel sheet from a sign up list into a cleaner output.

The form is collecting family registrations for an event. Each family registers multiple family members under one reg form which is a table (Person 1: Name, Age, Grade; Person 2: Name, Age, Grade... So the output data comes out like this:

(Cell A1) Person 1 Name | (Cell A2) Person 1 Age | (Cell A3) Person 1 Grade | (Cell A4) Person 2 Name | (Cell A5) Person 2 Age | (Cell A6) Person 2 Grade| ....

So all the data comes out all in one row in different columns. However I want to have all all the Ages in one column, Weights in one column, etc. in multiple rows.

(There are also other questions that pertain to the whole family, not each individual.)

Two questions:
Is there a better way to collect the data on the form (without making each person fill out their own form?)

Is the are way to automatically format the data into what I want using excel?

posted by roaring beast to Computers & Internet (10 answers total)

Response by poster: Sorry, that first example should be more like this (All data in one row with many columns):

(Cell A1) Person 1 Name | (Cell B1) Person 1 Age | (Cell C1) Person 1 Grade | (Cell D1) Person 2 Name | (Cell E1) Person 2 Age | (Cell F1) Person 2 Grade| ....
posted by roaring beast at 7:45 PM on October 27

I believe that Jotform will display ALL content associated with someone who clicks “submit” on one line. This is actually a good thing - it clarifies how many people (and which people) are in each party. Can you just manipulate the data after everyone is done submitting?
posted by samthemander at 7:55 PM on October 27

Response by poster: Yes, that's exactly the situation. But I'm wondering if there is a way to do it without having to manually copy and paste everything at the end.
posted by roaring beast at 8:59 PM on October 27

That does sound annoying. I don't have an excel to play with today, but what about this super-hacky idea:

Use the original file as a data source for a power-query query. Import only the first 3 columns. (select "only create connection" so theres no table output)
Do it again with a second query, importing only columns 4-6, then removing blanks
Again as many times as you think there will be family members. (It would be nice if there's a maximum number)

Then do a merge query merging all the previous queries into one table.
posted by ctmf at 9:42 PM on October 27

You'd probably have to adjust column titles in the queries as appropriate for that to work.
posted by ctmf at 9:44 PM on October 27

Drawback: that would lose the information about who's in the same family, which may or may not be important to you.
posted by ctmf at 9:47 PM on October 27

Serial commenting, sorry, trying not to edit previous comments - if keeping families together was important, you could include column 1 in every query (a duplicate for the first query) in a column named "family name" or "primary membership" or something like that. So every query would have "family name" "person name" "age" "grade".

Then a sort on "family name" would put families' records all adjacent.
posted by ctmf at 9:52 PM on October 27

The operation you want to perform is known as a pivot. Here is how you pivot in Power Query. You can launch Power Query directly from Excel.
posted by shock muppet at 12:51 AM on October 28 [1 favorite]

You don't have a way to move this into a SQL database, do you? You could do this pretty quickly with union/union all. If you don't have one yet I would also add a field in your spreadsheet for a unique identifier for each row, even just numbering each row starting with one, as well as a field telling you the original columns (i.e Person1 or Person2) so when you move the data around you know where it originated (apologies if you know this or already have one!).

If SQL is an option your query could look something like:

SELECT UniqueIdentifier as OriginalRow, "Person 1" as SourceFields, Person1Name as Name, Person1Age as Age, Person1Grade as Grade
FROM Spreadsheet where Person1Name IS NOT NULL OR Person1Age IS NOT NULL OR Person1Grade IS NOT NULL
UNION
SELECT UniqueIdentifier, "Person 2", Person2Name, Person2Age, Person2Grade
FROM Spreadsheet where Person2Name IS NOT NULL OR Person2Age IS NOT NULL OR Person2Grade IS NOT NULL
UNION
Copy Person2 query with new numbers for all the columns you have

(That was typed on my phone so apologies for any errors -- good luck!)
posted by an octopus IRL at 6:19 AM on October 28

Not a JotForm user and wading through their help docs isn't getting anywhere but if you can ask their support people, I wonder if it is possible to make changes to your form so that the output looks like:

RegistrationID,Name,Age,Grade
1,Tina,25,B
1,Greg,22,C
1,Doug,45,A
2,Jane,18,B
3,Mike,45,C
3,Emma,31,A

i.e. things are repeated vertically not horizontally and an added column is present so that you can group related people together.
posted by mmascolino at 6:45 AM on October 28