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.

 

[Jeffrey Mahoney]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Barcode Reader
2
3	Order
4	Location
5
6
7		Order	Date	Due Date	DEBURR	CNCPLASMA	PRESSBRAKE	SAW	LATHE1	LATHE2	MACHINING	DRILL	TACK	WELD1	WELD2	GRIND	PAINT	ASSEMBLY	COMPLETE		Order	Location
8		TFD0012																			TFD0012	CNCPLASMA
9		CHD0036																			CHD0036	SAW
10		GD0009																			GD0009	MACHINING
11		LE0012																			LE0012	DEBURR
12		HK0001																			HK0001	COMPLETE
13		THD0001																			THD0001	WELD2
Sheet1

Cell Formulas
Range	Formula
B8:B13	B8	=U8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E8:S13	Expression	=VLOOKUP($B8,$U:$V,2,0)=E$7	text	NO
E8:S13	Expression	=IFERROR(MATCH(VLOOKUP($B8,$U:$V,2,FALSE),F$7:$S$7,0)>0,FALSE)	text	NO