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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Thank you for the great tool. I don't know if this has been reported but This CF formula isn't showing the FILL correctly. Notice in the Cell Format, it should show the example text as black fill.


Book2
ABCDEFGHIJKLMNOPQRSTUV
1Barcode Reader
2
3Order
4Location
5
6
7OrderDateDue DateDEBURRCNCPLASMAPRESSBRAKESAWLATHE1LATHE2MACHININGDRILLTACKWELD1WELD2GRINDPAINTASSEMBLYCOMPLETEOrderLocation
8TFD0012TFD0012CNCPLASMA
9CHD0036CHD0036SAW
10GD0009GD0009MACHINING
11LE0012LE0012DEBURR
12HK0001HK0001COMPLETE
13THD0001THD0001WELD2
Sheet1
Cell Formulas
RangeFormula
B8:B13B8=U8
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E8:S13Expression=VLOOKUP($B8,$U:$V,2,0)=E$7textNO
E8:S13Expression=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)textNO
 

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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