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))
 

Some videos you may like

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
Joined
Aug 18, 2011
Messages
6,409
Office Version
365
Platform
Windows
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
Joined
Apr 15, 2019
Messages
19
OK great! I will try that out. I apologize for the late response. Busy holiday weekend where I'm at.

Thanks again!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top