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

Pratik T
Pratik T ✭
in Smartsheet Basics

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,

· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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:

    Screen Shot 2022-01-19 at 4.29.13 PM.png


    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:

    Screen Shot 2022-01-19 at 4.29.54 PM.png


    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:

    Screen Shot 2022-01-19 at 5.09.22 PM.png


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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:

    Screen Shot 2022-01-19 at 4.29.13 PM.png


    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:

    Screen Shot 2022-01-19 at 4.29.54 PM.png


    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:

    Screen Shot 2022-01-19 at 5.09.22 PM.png


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Pratik T
    Pratik T ✭

    Hi Genevieve,

    Very helpful and comprehensive response.

    Thank you!

    Best,

    Pratik

    · Share on FacebookShare on Twitter
  • Christina09
    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:

    image.png

    Sheet B:

    image.png

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

    Could you please help?

    Thanks.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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.

    Screen Shot 2022-07-15 at 11.25.14 PM.png


    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)

    Screen Shot 2022-07-15 at 11.25.32 PM.png


    For a full formula:

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

    Screen Shot 2022-07-15 at 11.27.29 PM.png


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

    Screen Shot 2022-07-15 at 11.30.30 PM.png

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

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Christina09
    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 :)

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Christina09
    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!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video