Any way to consolidate this rather brutish Excel formula?

WikeMo1

New Member
Joined
Apr 15, 2019
Messages
19
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!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))
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.


Excel 2013/2016
VWXYZAAABACADAEAFAG
1hello : world MrExcel : Microsoft : FormR : WikeMo1helloworldMrExcelMicrosoftFormRWikeMo1
WorkingData
Cell Formulas
RangeFormula
V1=myConcat(X1:AG1)
 
Last edited:
Upvote 0
OK great! I will try that out. I apologize for the late response. Busy holiday weekend where I'm at.

Thanks again!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top