XL2BB - Excel Range to BBCode 2.1

[smozgur]

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.

 

[SelamT]

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.

 

[Alex Blakenburg]

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
	A	B	C	D	E	F	G	H
1	Episode_ID	Current PCP	Department Name	Region		Current PCP	Department Name	Region
2	195922633	Andrews	City West	South		Andrews	City West	South
3	2058254
4	2258974536
5	2865876324	Gogh	City North	East		Gogh	City North	East
6
7
8		Filter				Xlookup
Sheet1

Cell Formulas
Range	Formula
B2:D2,B5:D5,B3:B4	B2	=FILTER(Master!$B$2:$D$7,Master!$A$2:$A$7=$A2,"")
F2:H2,F5:H5,F3:F4	F2	=XLOOKUP($A2,Master!$A$2:$A$7,Master!$B$2:$D$7,"")
Dynamic array formulas.

 

[smozgur]

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:

But the following looks much better:

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
	A	B	C	D	E	F	G	H
1	Episode_ID	Current PCP	Department Name	Region		Current PCP	Department Name	Region
2	195922633	a	e	a		a	e	a
3	2058254
4	2258974536
5	2865876324	d	g	f		d	g	f
6
7
8		Filter				Xlookup
Sheet1

Cell Formulas
Range	Formula
B2:D5	B2	=FILTER(Master!$B$2:$D$7,Master!$A$2:$A$7=$A2,{"","",""})
F2:H5	F2	=XLOOKUP($A2,Master!$A$2:$A$7,Master!$B$2:$D$7,{"","",""})
Dynamic array formulas.

 

[ozbeachbum]

Your Excel in your operating system doesn't work well with the clipboard via VBA and XL2BB has nothing to do with it.
However there is a workaround. Please check the "Manually copy the generated code" and retry.

View attachment 100889

Thanks,
Been searching for ages for a solution.