SORT-UNIQUE-FILTER [Workaround for Merged Cells]

[thewiseguy]

Hello all,

I am utilizing the SORT-UNIQUE-FILTER command in order to combine 2 product lists into 1 Bill of Materials. The items within this combined list need to be shipped and tracked and there are times when 1 item is shipped in multiple shipments. This is causing me to need multiple lines for 1 product and hence, a merged cell. I am noticing however that I can not use the SORT-UNIQUE-FILTER command, when cells are merged. You can see this in my first cell which is blank. Is there a workaround without VBA?

v2023.4 - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Mfg/Item	Item	Item Type	Manufacturer	Part #	Fix. Qty	Lamp Qty	PO #	Qty Ordered	Shipper	Tracking	ETA	Notes
2	#SPILL!	11W LED PL Lamp [GX24q-MCCT]	Lamp	MAXLITE	9PLG24QVCS		29
3
4
5
Shipping (BOM)

Cell Formulas
Range	Formula
A2	A2	=SORT(UNIQUE(FILTER(' Line Item (input)'!W3:W2002,(' Line Item (input)'!W3:W2002 <>"")*(' Line Item (input)'!W3:W2002<>0),FALSE)))
B2:G2	B2	='Product (BOM)'!B2

 

[Fluff]

Is there a workaround without VBA?

Yes, Don't used merged cells they are an abomination & should be avoided like the plague.

You can see this in my first cell which is blank

If you mean A it is not blank, it contains the #SPILL! error. The 3 cells below it are blank.
You simply cannot put spill formulae in merged cells.

 

[thewiseguy]

Yes, Don't used merged cells they are an abomination & should be avoided like the plague.
If you mean A it is not blank, it contains the #SPILL! error. The 3 cells below it are blank.
You simply cannot put spill formulae in merged cells.

ha! it's too late now; I've been my entire workbook around merged cells.

Thanks for the reply!

 

[Fluff]

In that case you are likely to have major problems. A lot of formulae do not like being in, or looking at merged cells. Also they can play havoc with VBA.

 

[Peter_SSs]

Apart from difficulties with merged cells, it is a bad idea to have a worksheet name with a leading (or trailing) space like you have.

It is not clear to me exactly what you are trying to achieve. I'm wondering if it might be this?
If not can you give us a very small sample of column W in the other sheet and show us your expected result (manually entered)?

23 07 21.xlsm
	A
1
2	a b c e g
3
4
5
6
Shipping (BOM)

Cell Formulas
Range	Formula
A2	A2	=TEXTJOIN(CHAR(10),,SORT(UNIQUE(FILTER(' Line Item (input)'!W3:W2002,(' Line Item (input)'!W3:W2002 <>"")*(' Line Item (input)'!W3:W2002<>0),FALSE))))