Any way to consolidate this rather brutish Excel formula?

W

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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

A
  • Solved
every time I run this code the auto filter only filters 1 column any ideals
Replies
1
Views
687
HaHoBe
HaHoBe
T
  • Solved
Do without loop error
Replies
4
Views
638
tispivey
T
N
  • Question Question
Dropdown code VBA doesn't execute
Replies
0
Views
503
niresh28
N
P
  • Solved
Average sales for the previous 3 months for each product
Replies
2
Views
341
paun_shotts
P
D
  • Question Question
Compile Error
Replies
2
Views
565
Kenneth Hobson
Kenneth Hobson

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

Share this page

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