Any way to consolidate this rather brutish Excel formula?

[WikeMo1]

I have an Excel formula that I put together to consolidate the content of a whole grid of cells into minimally formatted bullet lists. However I have had to cut it off before the character limit.

What I am doing is taking data exploded into discrete elements and pulling it into a cell, to re-"compile" it into a return delimited list formatted in a way that my company can use in technical documents. The data is arranged in pairs of two. Because of the source of the data, sometimes there are blank cells that I would have to skip, or the list becomes total garbage.

The data grid allows for 30 individual elements (I would love to be able to have more) or 15 pairs of parts A & B.
The formula arranges the items/pairs as follows based on four conditions:

- Item A and B = both empty = skip them
- Item A and B = both not empty = make line by adding bullet point, add item A + colon + space, add item B, insert carriage return
- Item A empty + item B not empty = make line by adding bullet point, add item B + colon + space, insert carriage return
- Item A not empty + item B empty = make line by adding bullet point, add item A + colon + space, insert carriage return

This skips all blank garbage results that would otherwise make the list break.

However, I am wondering if there is a more elegant way to write this and scale it, as it is the same logic over each cell pair.

Here is the formula:

=IFS(AND(WorkingData!X1="", WorkingData!Y1=""), "", AND(WorkingData!X1<>"", WorkingData!Y1<>""), CHAR(149)&CHAR(32)&WorkingData!X1&CHAR(58)&CHAR(32)&WorkingData!Y1&CHAR(10), AND(WorkingData!X1="", WorkingData!Y1<>""), CHAR(149)&CHAR(32)&WorkingData!Y1&CHAR(10), AND(WorkingData!X1<>"", WorkingData!Y1=""), CHAR(149)&CHAR(32)&WorkingData!X1&CHAR(10))
&IFS(AND(WorkingData!Z1="", WorkingData!AA1=""), "", AND(WorkingData!Z1<>"", WorkingData!AA1<>""), CHAR(149)&CHAR(32)&WorkingData!Z1&CHAR(58)&CHAR(32)&WorkingData!AA1&CHAR(10), AND(WorkingData!Z1="", WorkingData!AA1<>""), CHAR(149)&CHAR(32)&WorkingData!AA1&CHAR(10), AND(WorkingData!Z1<>"", WorkingData!AA1=""), CHAR(149)&CHAR(32)&WorkingData!Z1&CHAR(10))
&IFS(AND(WorkingData!AB1="", WorkingData!AC1=""), "", AND(WorkingData!AB1<>"", WorkingData!AC1<>""), CHAR(149)&CHAR(32)&WorkingData!AB1&CHAR(58)&CHAR(32)&WorkingData!AC1&CHAR(10), AND(WorkingData!AB1="", WorkingData!AC1<>""), CHAR(149)&CHAR(32)&WorkingData!AC1&CHAR(10), AND(WorkingData!AB1<>"", WorkingData!AC1=""), CHAR(149)&CHAR(32)&WorkingData!AB1&CHAR(10))
&IFS(AND(WorkingData!AD1="", WorkingData!AE1=""), "", AND(WorkingData!AD1<>"", WorkingData!AE1<>""), CHAR(149)&CHAR(32)&WorkingData!AD1&CHAR(58)&CHAR(32)&WorkingData!AE1&CHAR(10), AND(WorkingData!AD1="", WorkingData!AE1<>""), CHAR(149)&CHAR(32)&WorkingData!AE1&CHAR(10), AND(WorkingData!AD1<>"", WorkingData!AE1=""), CHAR(149)&CHAR(32)&WorkingData!AD1&CHAR(10))
&IFS(AND(WorkingData!AF1="", WorkingData!AG1=""), "", AND(WorkingData!AF1<>"", WorkingData!AG1<>""), CHAR(149)&CHAR(32)&WorkingData!AF1&CHAR(58)&CHAR(32)&WorkingData!AG1&CHAR(10), AND(WorkingData!AF1="", WorkingData!AG1<>""), CHAR(149)&CHAR(32)&WorkingData!AG1&CHAR(10), AND(WorkingData!AF1<>"", WorkingData!AG1=""), CHAR(149)&CHAR(32)&WorkingData!AF1&CHAR(10))
&IFS(AND(WorkingData!AH1="", WorkingData!AI1=""), "", AND(WorkingData!AH1<>"", WorkingData!AI1<>""), CHAR(149)&CHAR(32)&WorkingData!AH1&CHAR(58)&CHAR(32)&WorkingData!AI1&CHAR(10), AND(WorkingData!AH1="", WorkingData!AI1<>""), CHAR(149)&CHAR(32)&WorkingData!AI1&CHAR(10), AND(WorkingData!AH1<>"", WorkingData!AI1=""), CHAR(149)&CHAR(32)&WorkingData!AH1&CHAR(10))
&IFS(AND(WorkingData!AJ1="", WorkingData!AK1=""), "", AND(WorkingData!AJ1<>"", WorkingData!AK1<>""), CHAR(149)&CHAR(32)&WorkingData!AJ1&CHAR(58)&CHAR(32)&WorkingData!AK1&CHAR(10), AND(WorkingData!AJ1="", WorkingData!AK1<>""), CHAR(149)&CHAR(32)&WorkingData!AK1&CHAR(10), AND(WorkingData!AJ1<>"", WorkingData!AK1=""), CHAR(149)&CHAR(32)&WorkingData!AJ1&CHAR(10))
&IFS(AND(WorkingData!AL1="", WorkingData!AM1=""), "", AND(WorkingData!AL1<>"", WorkingData!AM1<>""), CHAR(149)&CHAR(32)&WorkingData!AL1&CHAR(58)&CHAR(32)&WorkingData!AM1&CHAR(10), AND(WorkingData!AL1="", WorkingData!AM1<>""), CHAR(149)&CHAR(32)&WorkingData!AM1&CHAR(10), AND(WorkingData!AL1<>"", WorkingData!AM1=""), CHAR(149)&CHAR(32)&WorkingData!AL1&CHAR(10))
&IFS(AND(WorkingData!AN1="", WorkingData!AO1=""), "", AND(WorkingData!AN1<>"", WorkingData!AO1<>""), CHAR(149)&CHAR(32)&WorkingData!AN1&CHAR(58)&CHAR(32)&WorkingData!AO1&CHAR(10), AND(WorkingData!AN1="", WorkingData!AO1<>""), CHAR(149)&CHAR(32)&WorkingData!AO1&CHAR(10), AND(WorkingData!AN1<>"", WorkingData!AO1=""), CHAR(149)&CHAR(32)&WorkingData!AN1&CHAR(10))
&IFS(AND(WorkingData!AP1="", WorkingData!AQ1=""), "", AND(WorkingData!AP1<>"", WorkingData!AQ1<>""), CHAR(149)&CHAR(32)&WorkingData!AP1&CHAR(58)&CHAR(32)&WorkingData!AQ1&CHAR(10), AND(WorkingData!AP1="", WorkingData!AQ1<>""), CHAR(149)&CHAR(32)&WorkingData!AQ1&CHAR(10), AND(WorkingData!AP1<>"", WorkingData!AQ1=""), CHAR(149)&CHAR(32)&WorkingData!AP1&CHAR(10))
&IFS(AND(WorkingData!AR1="", WorkingData!AS1=""), "", AND(WorkingData!AR1<>"", WorkingData!AS1<>""), CHAR(149)&CHAR(32)&WorkingData!AR1&CHAR(58)&CHAR(32)&WorkingData!AS1&CHAR(10), AND(WorkingData!AR1="", WorkingData!AS1<>""), CHAR(149)&CHAR(32)&WorkingData!AS1&CHAR(10), AND(WorkingData!AR1<>"", WorkingData!AS1=""), CHAR(149)&CHAR(32)&WorkingData!AR1&CHAR(10))
&IFS(AND(WorkingData!AT1="", WorkingData!AU1=""), "", AND(WorkingData!AT1<>"", WorkingData!AU1<>""), CHAR(149)&CHAR(32)&WorkingData!AT1&CHAR(58)&CHAR(32)&WorkingData!AU1&CHAR(10), AND(WorkingData!AT1="", WorkingData!AU1<>""), CHAR(149)&CHAR(32)&WorkingData!AU1&CHAR(10), AND(WorkingData!AT1<>"", WorkingData!AU1=""), CHAR(149)&CHAR(32)&WorkingData!AT1&CHAR(10))
&IFS(AND(WorkingData!AV1="", WorkingData!AW1=""), "", AND(WorkingData!AV1<>"", WorkingData!AW1<>""), CHAR(149)&CHAR(32)&WorkingData!AV1&CHAR(58)&CHAR(32)&WorkingData!AW1&CHAR(10), AND(WorkingData!AV1="", WorkingData!AW1<>""), CHAR(149)&CHAR(32)&WorkingData!AW1&CHAR(10), AND(WorkingData!AV1<>"", WorkingData!AW1=""), CHAR(149)&CHAR(32)&WorkingData!AV1&CHAR(10))
&IFS(AND(WorkingData!AX1="", WorkingData!AY1=""), "", AND(WorkingData!AX1<>"", WorkingData!AY1<>""), CHAR(149)&CHAR(32)&WorkingData!AX1&CHAR(58)&CHAR(32)&WorkingData!AY1&CHAR(10), AND(WorkingData!AX1="", WorkingData!AY1<>""), CHAR(149)&CHAR(32)&WorkingData!AY1&CHAR(10), AND(WorkingData!AX1<>"", WorkingData!AY1=""), CHAR(149)&CHAR(32)&WorkingData!AX1&CHAR(10))
&IFS(AND(WorkingData!AZ1="", WorkingData!BA1=""), "", AND(WorkingData!AZ1<>"", WorkingData!BA1<>""), CHAR(149)&CHAR(32)&WorkingData!AZ1&CHAR(58)&CHAR(32)&WorkingData!BA1&CHAR(10), AND(WorkingData!AZ1="", WorkingData!BA1<>""), CHAR(149)&CHAR(32)&WorkingData!BA1&CHAR(10), AND(WorkingData!AZ1<>"", WorkingData!BA1=""), CHAR(149)&CHAR(32)&WorkingData!AZ1&CHAR(10))
&IFS(AND(WorkingData!BB1="", WorkingData!BC1=""), "", AND(WorkingData!BB1<>"", WorkingData!BC1<>""), CHAR(149)&CHAR(32)&WorkingData!BB1&CHAR(58)&CHAR(32)&WorkingData!BC1&CHAR(10), AND(WorkingData!BB1="", WorkingData!BC1<>""), CHAR(149)&CHAR(32)&WorkingData!BC1&CHAR(10), AND(WorkingData!BB1<>"", WorkingData!BC1=""), CHAR(149)&CHAR(32)&WorkingData!BB1&CHAR(10))
&IFS(AND(WorkingData!BD1="", WorkingData!BE1=""), "", AND(WorkingData!BD1<>"", WorkingData!BE1<>""), CHAR(149)&CHAR(32)&WorkingData!BD1&CHAR(58)&CHAR(32)&WorkingData!BE1&CHAR(10), AND(WorkingData!BD1="", WorkingData!BE1<>""), CHAR(149)&CHAR(32)&WorkingData!BE1&CHAR(10), AND(WorkingData!BD1<>"", WorkingData!BE1=""), CHAR(149)&CHAR(32)&WorkingData!BD1&CHAR(10))
&IFS(AND(WorkingData!BF1="", WorkingData!BG1=""), "", AND(WorkingData!BF1<>"", WorkingData!BG1<>""), CHAR(149)&CHAR(32)&WorkingData!BF1&CHAR(58)&CHAR(32)&WorkingData!BG1&CHAR(10), AND(WorkingData!BF1="", WorkingData!BG1<>""), CHAR(149)&CHAR(32)&WorkingData!BG1&CHAR(10), AND(WorkingData!BF1<>"", WorkingData!BG1=""), CHAR(149)&CHAR(32)&WorkingData!BF1&CHAR(10))
&IFS(AND(WorkingData!BH1="", WorkingData!BI1=""), "", AND(WorkingData!BH1<>"", WorkingData!BI1<>""), CHAR(149)&CHAR(32)&WorkingData!BH1&CHAR(58)&CHAR(32)&WorkingData!BI1&CHAR(10), AND(WorkingData!BH1="", WorkingData!BI1<>""), CHAR(149)&CHAR(32)&WorkingData!BI1&CHAR(10), AND(WorkingData!BH1<>"", WorkingData!BI1=""), CHAR(149)&CHAR(32)&WorkingData!BH1&CHAR(10))
&IFS(AND(WorkingData!BJ1="", WorkingData!BK1=""), "", AND(WorkingData!BJ1<>"", WorkingData!BK1<>""), CHAR(149)&CHAR(32)&WorkingData!BJ1&CHAR(58)&CHAR(32)&WorkingData!BK1&CHAR(10), AND(WorkingData!BJ1="", WorkingData!BK1<>""), CHAR(149)&CHAR(32)&WorkingData!BK1&CHAR(10), AND(WorkingData!BJ1<>"", WorkingData!BK1=""), CHAR(149)&CHAR(32)&WorkingData!BJ1&CHAR(10))
&IFS(AND(WorkingData!BL1="", WorkingData!BM1=""), "", AND(WorkingData!BL1<>"", WorkingData!BM1<>""), CHAR(149)&CHAR(32)&WorkingData!BL1&CHAR(58)&CHAR(32)&WorkingData!BM1&CHAR(10), AND(WorkingData!BL1="", WorkingData!BM1<>""), CHAR(149)&CHAR(32)&WorkingData!BM1&CHAR(10), AND(WorkingData!BL1<>"", WorkingData!BM1=""), CHAR(149)&CHAR(32)&WorkingData!BL1&CHAR(10))
&IFS(AND(WorkingData!BN1="", WorkingData!BO1=""), "", AND(WorkingData!BN1<>"", WorkingData!BO1<>""), CHAR(149)&CHAR(32)&WorkingData!BN1&CHAR(58)&CHAR(32)&WorkingData!BO1&CHAR(10), AND(WorkingData!BN1="", WorkingData!BO1<>""), CHAR(149)&CHAR(32)&WorkingData!BO1&CHAR(10), AND(WorkingData!BN1<>"", WorkingData!BO1=""), CHAR(149)&CHAR(32)&WorkingData!BN1&CHAR(10))

 

[FormR]

Hi, how about trying a user defined function.

Function myConcat(r As Range)
Dim c As Range
For i = 1 To r.Columns.Count Step 2
    Select Case Application.CountA(r.Cells(1, i), r.Cells(1, i + 1))
        Case 1: myConcat = myConcat & Chr(10) & Chr(149) & r.Cells(1, i) & r.Cells(1, i + 1) & " : "
        Case 2: myConcat = myConcat & Chr(10) & Chr(149) & r.Cells(1, i) & " : " & r.Cells(1, i + 1)
    End Select
Next i
myConcat = Mid(myConcat, 2)
End Function

To use..

1. With your workbook active press ALT+F11 to open the VB Editor
2. In the VBE click "Insert" > "Module"
3. Copy the code above into the large code window on the top right
4. Save your workbook as a macro enabled workbook .XLSM
5. Use as demonstrated below.

Excel 2013/2016
	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	hello : world MrExcel : Microsoft : FormR : WikeMo1		hello	world	MrExcel			Microsoft			FormR	WikeMo1
WorkingData

Cell Formulas
Range	Formula
V1	=myConcat(X1:AG1)

 

[WikeMo1]

OK great! I will try that out. I apologize for the late response. Busy holiday weekend where I'm at.

Thanks again!