0

I have spreadsheet with 50+ rows of project installation data, that is separated across 5 different columns. I need to provide a summary of each project ID, with the results of each install data columns summarized into 1 cell as text.

Is there a way to collate the value for each cell row in Columns B-F, into 1 summary text cell where the result of each row matches the heading of that Column?

In my screenshot below, cell G2 is a sample of the desired format of the final summary result, however I want the data in cells B2:F2 to appear next to each relevant heading.

I have tried a few things but cat work out how to get the format of the final summary result

Can anyone help with this problem?

enter image description here

Improve this question
4
  • Try to use TEXTJOIN() worksheet function - it is available in your application version.
    – Akina
    Dec 4, 2019 at 8:07
  • I have never used that before. How does it work?
    – Rohan F
    Dec 4, 2019 at 8:29
  • TEXTJOIN function
    – Akina
    Dec 4, 2019 at 8:32
  • Use ":" & CHAR(10) & "- " as delimiter, and delete 2 chars from each side of TEXTLOIN() result.
    – Akina
    Dec 4, 2019 at 8:45

1 Answer 1

Reset to default
1

In G2, put :

="- "&$B$1&" : "&B2&CHAR(10)&"- "&$C$1&" : "&C2&CHAR(10)&"- "&$D$1&" : "&D2&CHAR(10)&"- "&$E$1&" : "&E2&CHAR(10)&"- "&$F$1&" : "&F2

and drag downwards. Make sure "wrap text" option in the cell format is selected too.

idea : $ is used to lock the formula column/row. char(10) is for the 'newline' character. & symbol functions similar to concatenate or textjoin excel function, just imho more straightforward (that's why I use it).

kindly share if it works/not. (:

Improve this answer
3
  • Hi there p._phidot,yes that worked well thanks so much. I have never used the CHAR(10) formula, so this is very handy to know
    – Rohan F
    Dec 5, 2019 at 1:12
  • Credit to the initial comment, I just learnt it from @Akina. Btw, if you accept this answer please click the tick to remove this question from the 'unanswered' list. ( :
    – p._phidot_
    Dec 5, 2019 at 1:33
  • Yes thanks to both Akina and p._phidot for helping me solve this excel problem!
    – Rohan F
    Dec 6, 2019 at 2:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .