EXCEL - Create a bulleted list in a single cell from a list [Solved/Closed]

Report
Posts
2
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 12, 2013
-
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
-
Hello,

I'd greatly appreciate some help with this:

I'm trying to get a list of data in cell "D5" only from cells "A1:A10" that do have information. I would like to have them separated by commas or, if possible, in a bulleted list.

I'm fairly new to this so I don't know if this could be done with formulas or with a Macro.

Thanks in advance.


Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
Hi Morocog,

Separated by commas the formula would look like:
=IF(A1="","",A1)&IF(A2="","",","&A2)&IF(A3="","",","&A3)&IF(A4="","",","&A4)&IF(A5="","",","&A5)&IF(A6="","",","&A6)&IF(A77="","",","&A7)&IF(A8="","",","&A8)&IF(A9="","",","&A9)&IF(A10="","",","&A10)

For the bulleted list we will have to use macro:
Sub CreateBulletListInD5()

If Range("A1").Value <> vbNullString Then Range("D5").Value = Range("A1").Value
If Range("D5").Value <> vbNullString And Range("A2").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A2").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A2").Value
If Range("D5").Value <> vbNullString And Range("A3").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A3").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A3").Value
If Range("D5").Value <> vbNullString And Range("A4").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A4").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A4").Value
If Range("D5").Value <> vbNullString And Range("A5").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A5").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A5").Value
If Range("D5").Value <> vbNullString And Range("A6").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A6").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A6").Value
If Range("D5").Value <> vbNullString And Range("A7").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A7").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A7").Value
If Range("D5").Value <> vbNullString And Range("A8").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A8").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A8").Value
If Range("D5").Value <> vbNullString And Range("A9").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A9").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A9").Value
If Range("D5").Value <> vbNullString And Range("A10").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A10").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A10").Value

End Sub

Why would you want to put more info into a single cell? This will make it harder to work with the data. Just curious what the reason could be.

Best regards,
Trowa
morocog
Posts
2
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 12, 2013

Thanks for taking the time to help me out.
The reason why I need this bulleted list is because it is the final result of a series of formulas working with text and I need a summary, in one cell, that could be printed (without the need of working with that data anymore), I know how odd it sounds. You were really helpful, both the formula and the macro worked like a charm.
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
It sure sounds odd to me, but I'm glad I was able to help.