Multiple columns on form sheet into one for reporting.

Charles Osborn

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,

jmyzk_cloudsmart_jp

Hi @Charles Osborn

This is an interesting question. Here is one way to combine the two columns into one.

https://app.smartsheet.com/b/publish?EQBCT=1350c1b711c74371b720d09e356c9f22

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".

• 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, where Row 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.

Courtney S.

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.

Cory Page

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?

Charles Osborn

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.

jmyzk_cloudsmart_jp

Hi @Charles Osborn

This is an interesting question. Here is one way to combine the two columns into one.

https://app.smartsheet.com/b/publish?EQBCT=1350c1b711c74371b720d09e356c9f22

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".

• 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, where Row 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.

Courtney S.

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.