Consoldating a Comments Box

[Jay_hl]

Hi All

Im building a report, but have a requirement to consolidate a bunch of feedback line entries into a single wraped text cell, with the comments on each new line.

Ideally I would not like to "hard code" the column it looks up, as this might change place on the report. Some kind of match would be nice.

Attached is a spread sheet showing an idea of what I would like to achieve.

As ever, thanks for your help.

Jay

[Jay_hl]

If anyone could come up with something clever, that would be really appreciated

Jay

[Lemice]

Hello Jay,

First, format column I, make it Wrap Text and a bit wider
Then in I2, type

Please Login or Register  to view this content.

and in I3, paste this formula

Please Login or Register  to view this content.

down to I17

Then on H26, type

Please Login or Register  to view this content.

And here is your file with formula

(This feels like cheating though)

[Kamboj]

you can also use this vba code
Sub comnt()
Dim a As String
Dim Rng As Range
Sheet1.Range("H26,H29").ClearContents
For Each Rng In Range("H3:H14")
If Rng <> "" Then
Sheet1.Range("h26") = Trim(Sheet1.Range("h26")) & Chr(10) & ">" & Rng.Value
End If
Next
For Each Rng In Range("M3:M16")
If Rng <> "" Then
Sheet1.Range("h29") = Trim(Sheet1.Range("h29")) & Chr(10) & ">" & Rng.Value
End If
Next
End Sub