Multiple columns on form sheet into one for reporting.
I have a form that is asking the same question mulitple times. Can I now combine the data into one column on a sheet for reporting? I have Mess and Shuttle, but can't think of the logic to do it. Thanks,
Best Answers

This is an interesting question. Here is one way to combine the two columns into one.
Formulas
[Term] =IF(MOD([No.]@row, 2) = 1,
JOIN(COLLECT({Terms Definitions from Form : Term #1}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2))),
JOIN(COLLECT({Terms Definitions from Form : Term #2}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2)))
)[Term Definition] =IF(MOD([No.]@row, 2) = 1,
JOIN(COLLECT({Terms Definitions from Form : Term Definition #1}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2))),
JOIN(COLLECT({Terms Definitions from Form : Term Definition #2}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2)))
)[No.]
column: This column is a numbering sequence (1, 2, 3, …) which helps in alternating between "Term #1" and "Term #2" data.
MOD([No.]@row, 2) = 1
: This part checks whether the row number ([No.]@row
) is odd or even. If the row number is odd (
= 1
), it will pull "Term #1".  If the row number is even (
= 0
), it will pull "Term #2".
 If the row number is odd (
JOIN(COLLECT(...))
:COLLECT({Terms Definitions from Form : Term #1}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2))
: This part fetches "Term #1" from the original form sheet, whereRow ID
matches the row’s associated ID.INT(([No.]@row + 1) / 2)
calculates the correct Row ID to match with[No.]
row, essentially pairing each alternate row with either "Term #1" or "Term #2".JOIN(...)
combines any matched values into a single result. (You can use the INDEX(MATCH()) formula, but by using JOIN(COLLECT()), you can avoid error handling.
 Result: Each row in the
[Term]
column of the new sheet will alternate between "Term #1" and "Term #2" entries from the form sheet, creating a single column of terms.
I added Row ID to the sheet that gets input from a form.

One way just involves helper sheets: have one helper sheet use VLOOKUP or INDEX/MATCH to pull in entries only from the Term #1 columns, one helper sheet do the same thing with the Term #2 columns, and so on. Make sure all the helper sheets have the same column titles & column types (eg, the column titles in all the helper sheets are "Term" and "Definition"). Then make a Report of all the helper sheets together  that report will have everything. If multiple sheets are pulled into a report, if they have columns with the same type/title, those columns all pull into the same column in the report.
Answers

Are you looking to just combine the row "Term Definitions @#1" and #2? for just that one row?
Create new column: Formula: Term Definitions #1 +", "+ Term Definition #2. You could replace , with anything.
Or am I miss understanding your question?

Hi Corry,
I am looking for a new columns with Term Definitions 1 and Definitions 2 all in a signle column so i can report on them, count duplicates that sort of thing.

This is an interesting question. Here is one way to combine the two columns into one.
Formulas
[Term] =IF(MOD([No.]@row, 2) = 1,
JOIN(COLLECT({Terms Definitions from Form : Term #1}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2))),
JOIN(COLLECT({Terms Definitions from Form : Term #2}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2)))
)[Term Definition] =IF(MOD([No.]@row, 2) = 1,
JOIN(COLLECT({Terms Definitions from Form : Term Definition #1}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2))),
JOIN(COLLECT({Terms Definitions from Form : Term Definition #2}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2)))
)[No.]
column: This column is a numbering sequence (1, 2, 3, …) which helps in alternating between "Term #1" and "Term #2" data.
MOD([No.]@row, 2) = 1
: This part checks whether the row number ([No.]@row
) is odd or even. If the row number is odd (
= 1
), it will pull "Term #1".  If the row number is even (
= 0
), it will pull "Term #2".
 If the row number is odd (
JOIN(COLLECT(...))
:COLLECT({Terms Definitions from Form : Term #1}, {Terms Definitions from Form : Row ID}, INT(([No.]@row + 1) / 2))
: This part fetches "Term #1" from the original form sheet, whereRow ID
matches the row’s associated ID.INT(([No.]@row + 1) / 2)
calculates the correct Row ID to match with[No.]
row, essentially pairing each alternate row with either "Term #1" or "Term #2".JOIN(...)
combines any matched values into a single result. (You can use the INDEX(MATCH()) formula, but by using JOIN(COLLECT()), you can avoid error handling.
 Result: Each row in the
[Term]
column of the new sheet will alternate between "Term #1" and "Term #2" entries from the form sheet, creating a single column of terms.
I added Row ID to the sheet that gets input from a form.

One way just involves helper sheets: have one helper sheet use VLOOKUP or INDEX/MATCH to pull in entries only from the Term #1 columns, one helper sheet do the same thing with the Term #2 columns, and so on. Make sure all the helper sheets have the same column titles & column types (eg, the column titles in all the helper sheets are "Term" and "Definition"). Then make a Report of all the helper sheets together  that report will have everything. If multiple sheets are pulled into a report, if they have columns with the same type/title, those columns all pull into the same column in the report.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!