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,
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.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!