smozgur

XL2BB - Excel Range to BBCode 2.1

No permission to download
Hi smozgur,

Just an FYI, but I noticed the software version still shows 2.0.0 and not 2.1 in the File Properties.
I'm confident the add-in is the version specified in the forum based on the "Date last saved" is 2023-09-17.
I often keep several versions of software and add-ins on hand and need the files to reflect their version numbers and noticed this just now.
Best regards,
Thanks for reporting this, @zero269.
I kind of remember that I had an issue setting the Version field on Windows11, and then I forgot to revisit that before releasing the update. I'll check that in the next update.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi @smozgur,

I also manage an Excel site in Türkiye. The software I use is XenForo, just like this site.
Can I buy these add-on from you and use them on my own site?
It really makes a big difference for an Excel site.
If you sell this add-on, I would like to buy and use it.
Thank you very much.
 
XL2BB Breaking up range based on Result

Interesting this is the same formula and would normally produce a single range.

20240716 XLookup get entire row drewber.xlsx
ABCDEFGH
1Episode_IDCurrent PCPDepartment NameRegionCurrent PCPDepartment NameRegion
2195922633AndrewsCity WestSouthAndrewsCity WestSouth
32058254  
42258974536  
52865876324GoghCity NorthEastGoghCity NorthEast
6
7
8FilterXlookup
Sheet1
Cell Formulas
RangeFormula
B2:D2,B5:D5,B3:B4B2=FILTER(Master!$B$2:$D$7,Master!$A$2:$A$7=$A2,"")
F2:H2,F5:H5,F3:F4F2=XLOOKUP($A2,Master!$A$2:$A$7,Master!$B$2:$D$7,"")
Dynamic array formulas.
 
Last edited by a moderator:
I understand why this happens. The if_empty value is a single value (B3:D3) rather than an array, which the formula is supposed to return if a match is found (B2:D2). This cell now holds a scalar value instead of an array. This behavior is typical for functions that return both scalar and array values. If XL2BB were designed to detect such formulas in different cells (which is actually not practical), it would result in multiple identical functions being returned for B2:D2, B5:D5, and B3:B4 as separate rows in the cell formulas metadata table. In this case, yes, it might look a bit confusing, but still generating a valid formulas table.

If I was the developer of these functions, then I would design the functions to return compatible results by the provided parameters, i.e. if the return_array consists of multiple columns, then return the same if_empty value as the same number of columns.

The following confuses me as an Excel user:

scalar-vs-array.gif


But the following looks much better:
scalar-vs-array-better.gif


The following sample explains the behavior where I defined an array value for the if_empty parameter. Of course, this is not a use case, but only to explain XL2BB's behavior in such case.

Book5
ABCDEFGH
1Episode_IDCurrent PCPDepartment NameRegionCurrent PCPDepartment NameRegion
2195922633aeaaea
32058254  
42258974536  
52865876324dgfdgf
6
7
8FilterXlookup
Sheet1
Cell Formulas
RangeFormula
B2:D5B2=FILTER(Master!$B$2:$D$7,Master!$A$2:$A$7=$A2,{"","",""})
F2:H5F2=XLOOKUP($A2,Master!$A$2:$A$7,Master!$B$2:$D$7,{"","",""})
Dynamic array formulas.
 

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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