Create report with text from multiple columns but stacked vertically in rows?

Pratik T

We have text heavy smartsheet with below columns:

Company Name | Question | Summary Response | Answer 1 | Answer 2 | Answer 3 | Answer 4

The 'company name' column contains various values.

We want to prepare a report for a specific company as below:

Company:

• Question 1
• - Summary Response
• - Answer 1
• - Answer 2
• - Answer 3
• - Answer 4
• 
  
• Question 2
• - Summary Response
• - Ans....[]

How to accomplish this?

Best,

Genevieve P.

Hi @Pratik T

There isn't an automatic way to shift your sheet in a Report to display vertically; in a Report you can filter down by the Company Name and then display rows (still horizontally) for that company. See: Use Filters on Reports

You could, however, review a single row displayed vertically, either by Sending the row to your email or by double-clicking on the row number to open it up in the Edit Row window.

As a final option, you could potentially build out a sheet with a cross-sheet formula to bring in the content in the format you'd like.

In this instance, you'd need to set up a Helper Column in your source sheet that combines both the Company Name and the Question together into one cell so that we can use this as a Unique Identifier for that row, based on the Company.

You'll also need to add a Top Row to your sheet which replicates the Column Names so we can reference this in a formula, like so:

Then in a second sheet for this specific company, structure the sheet so that you have a Parent Row with the Company Name and Question together, with each of your columns below it as child rows:

In your second column, you can use this formula to auto-populate data from your first sheet:

=INDEX({Company Report Entire Sheet}, MATCH(PARENT([Primary Column]@row), {Helper Column}, 0), MATCH([Primary Column]@row, {Top Row}, 0))

{Company Report Entire Sheet} - this range looks at your entire source Sheet.

Then it MATCHES the Parent row data (the Company & Question) to the {Helper Column} that we created in order to find the ROW to bring back information from.

Then {Top Row} is a reference to the Top Row in the sheet where we have the same column titles listed in our Primary Column, to find the Column to bring back.

Final result:

Cheers,

Genevieve

Genevieve P.

Hi @Pratik T

There isn't an automatic way to shift your sheet in a Report to display vertically; in a Report you can filter down by the Company Name and then display rows (still horizontally) for that company. See: Use Filters on Reports

You could, however, review a single row displayed vertically, either by Sending the row to your email or by double-clicking on the row number to open it up in the Edit Row window.

As a final option, you could potentially build out a sheet with a cross-sheet formula to bring in the content in the format you'd like.

In this instance, you'd need to set up a Helper Column in your source sheet that combines both the Company Name and the Question together into one cell so that we can use this as a Unique Identifier for that row, based on the Company.

You'll also need to add a Top Row to your sheet which replicates the Column Names so we can reference this in a formula, like so:

Then in a second sheet for this specific company, structure the sheet so that you have a Parent Row with the Company Name and Question together, with each of your columns below it as child rows:

In your second column, you can use this formula to auto-populate data from your first sheet:

=INDEX({Company Report Entire Sheet}, MATCH(PARENT([Primary Column]@row), {Helper Column}, 0), MATCH([Primary Column]@row, {Top Row}, 0))

{Company Report Entire Sheet} - this range looks at your entire source Sheet.

Then it MATCHES the Parent row data (the Company & Question) to the {Helper Column} that we created in order to find the ROW to bring back information from.

Then {Top Row} is a reference to the Top Row in the sheet where we have the same column titles listed in our Primary Column, to find the Column to bring back.

Final result:

Cheers,

Genevieve

Pratik T

Hi Genevieve,

Very helpful and comprehensive response.

Thank you!

Best,

Pratik

Christina09

Hi @Genevieve P.

What if I'm trying to achieve this but in a horizontal view? I tried different formulas, index match match, collect...and none worked for me.

Sheet A:

Sheet B:

the helper column date is in txt/number so it would match sheet A.

Could you please help?

Thanks.

Genevieve P.

Hi @Christina09

You can use the third part of the INDEX function to identify what column you want to bring data back from.

Here's the structure of an INDEX function:

=INDEX({Range with Data to Return}, Row Number, Column Number)

In your instance, the first range would be the entire sheet, or all of your columns. Click on one column name, then hold the Shift button down and click your last column name.

Then for the Row Number you can manually put this in, since you only have one row per Stock.

=INDEX({Whole Sheet}, 2,

For the last bit of the formula, we'll use a MATCH function to match the date in your helper column to the correct top row, so we can find the right column:

MATCH([Helper Date]@row, {Top Row}, 0)

For a full formula:

=INDEX({Whole Sheet}, 2, MATCH([Helper Date]@row, {Top Row}, 0))

For Stock B, then you'll only need to change the row 2 in the middle to the number 3:

And so on. Let me know if this works for you!

Cheers,

Genevieve

Christina09

Thanks for getting back @Genevieve P. .

The formula works! However, is it possible to add the formula to look for "stock A" instead of entering # row in the index formula? There is a high chance that there will be more rows inserted in between stock A and stock B, and that would mess up the row # in the index formula.

Thank you for your help :)

Genevieve P.

Hi @Christina09

Yes! In this case, we would replace the row number with another MATCH function, finding the matching row this time.

MATCH("Stock A", {Primary Column}, 0)

The {Reference} would just be for that one column with the stock names. This would be the full formula structure:

=INDEX({Whole Sheet}, MATCH("Stock A", {Primary Column}, 0), MATCH([Helper Date]@row, {Top Row}, 0))

Cheers,

Genevieve

Christina09

@Genevieve P. ,

Thank you, this works perfectly.

One last question, is it possible to add more criteria to the formula? Normally I would use index/collect, but in vertical view, not sure how that'll work.

TIA!

Genevieve P.

Hi @Christina09

I have to admit I'm not sure. It may depend on what your criteria is? This formula works because the MATCH function finds the row number in the middle of the INDEX, and then at the end it matches up the correct column to use.

I would suggest having your extra criteria be built out in a different column, one that you can use to Match against instead of the Primary Column with the stock. (E.g., add the Stock Name and another cell together in both sheets to then use this unique cell to match).