# Any way to consolidate this rather brutish Excel formula?

#### WikeMo1

##### New Member
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:
Code:
``````=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!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))``````

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### FormR

##### MrExcel MVP
Hi, how about trying a user defined function.

Code:
``````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.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="background-color: #FFFF00;;">•hello : world
•MrExcel :
•Microsoft :
•FormR : WikeMo1</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">hello</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">world</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MrExcel</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">Microsoft</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FormR</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">WikeMo1</td></tr></tbody></table><p style="width:8.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">WorkingData</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">V1</th><td style="text-align:left">=myConcat(<font color="Blue">X1:AG1</font>)</td></tr></tbody></table></td></tr></table><br />

Last edited:

#### WikeMo1

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

Thanks again!

Last edited:

1,102,134
Messages
5,484,935
Members
407,475
Latest member
Dix_Fix

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...