Simpler/faster way to filter unique list

[danhendo888]

The below table is my data.

My worksheet consists of order numbers. In my desired output I am trying to put a unique list of part names as shown below.

Currently I am using this formula =ARRAYTOTEXT(SORT(UNIQUE(FILTER(C2:C7,(A2:A7=A16)*(B2:B7="R"))))) but it seems to make my excel file operate slowly whenever I move around the workbook.

Would there be a faster formula to achieve the same output using formulas?

Book1
	A	B	C
1	Order Number	R/E	Part
2	593924	R	Oil
3	593924	R	Wheel
4	593924	R	Tyre
5	111111111	E
6	111111111	E
7	111111111	E
8
9
10
11
12
13
14
15	Order Number
16	593924	Oil, Tyre, Wheel
Sheet1

Cell Formulas
Range	Formula
B16	B16	=ARRAYTOTEXT(SORT(UNIQUE(FILTER(C2:C7,(A2:A7=A16)*(B2:B7="R")))))

 

[hagia_sofia]

You could try to replace ARRAYTOTEXT by TEXTJOIN but the functions you are using should not slow down your workbook. Any chance you are e.g. using some volatile functions like OFFSET?

 

[danhendo888]

I have the below formulas across many columns in just one sheet. But it's very slow when auto refreshing.
I'm planning to copy paste these formulas onto more than 20 sheets in the same excel workbook, so I'm not sure how that would work considering it takes long to update on just the one sheet at the moment

=SUMIFS($AD:$AD,$R:$R,$A1182:$A1350,$AA:$AA,"R")

=SUMIFS($AD:$AD,$R:$R,$A1182:$A1350,$S:$S,K$1179)+SUMIFS($AD:$AD,$R:$R,$A1182:$A1350,$S:$S,K$1180)+SUMIFS($AD:$AD,$R:$R,$A1182:$A1350,$S:$S,K$1178)

=IF(COUNTIFS($S:$S,K1179,$R:$R,$A1182:$A1350,$AA:$AA,"r"),"Yes", "No")

=IFERROR(ARRAYTOTEXT(SORT(UNIQUE(FILTER(FILTER($S:$S,($R:$R='Sheet2'!$A1182)*($AA:$AA="R")),ISNA(XMATCH(FILTER($S:$S,($R:$R='Sheet2'!$A1182)*($AA:$AA="R")),$AQ$1174:$AQ$1180)))))),"")

=ARRAYTOTEXT(SORT(UNIQUE(FILTER($S:$S,($R:$R='Sheet2'!$A1182)*($AA:$AA="E")))))

 

[Fluff]

Get rid of the whole column references in the last two formulae, that is probably what is slowing down the file.
Also you can change the 2nd formula to

=sum(SUMIFS($AD:$AD,$R:$R,$A1182:$A1350,$S:$S,torow(K$1178:K$1180)))

 

[danhendo888]

MrExcel.xlsm
	U	V	W		Y
24464	Shipment Number	Code	Revenue or Expense		USD
24465	236371901	Wheel	R		(10.00)
24466	236371901	Gearbox	E		2.00
24467	236371901	Tyre	R		(22.00)
24468	236371901	Gas	E		5.00
24469	236371901	Fuel	E		10.00
24470	236371901	Rims	R		(4.00)
24471	236371922	Tyre	R		(44.00)
24472	236371922	Gas	E		20.00
24473	236371922	Fuel	E		50.00
24474	236371922	Rims	R		(100.00)
Raw Data

MrExcel.xlsm
	R	S	T
24477	SUM	Tyre
24478		Rims
24479	Fluff's Formula Test:
24480	236371901	-170
24481	236371922
24482
24483
24484
24485
24486
24487	Get unique Codes but	R	E
24488	Exclude These -->	Wheel	Gas
24489
24490	236371901	Rims, Tyre	Fuel, Gearbox
24491
24492		R	E
24493	All unique codes -->	Rims, Tyre, Wheel	Fuel, Gas, Gearbox
Raw Data

Cell Formulas
Range	Formula
S24480	S24480	=SUM(SUMIFS($Y:$Y,$U:$U,R24480:R24481,$V:$V,TOROW(S24477:S24478)))
S24490	S24490	=IFERROR(ARRAYTOTEXT(SORT(UNIQUE(FILTER(FILTER('Raw Data'!$V:$V,('Raw Data'!$U:$U=R24490)*('Raw Data'!$W:$W="R")),ISNA(XMATCH(FILTER('Raw Data'!$V:$V,('Raw Data'!$U:$U=R24490)*('Raw Data'!$W:$W="R")),S24487:S24488)))))),"")
T24490	T24490	=IFERROR(ARRAYTOTEXT(SORT(UNIQUE(FILTER(FILTER('Raw Data'!$V:$V,('Raw Data'!$U:$U=R24490)*('Raw Data'!$W:$W="e")),ISNA(XMATCH(FILTER('Raw Data'!$V:$V,('Raw Data'!$U:$U=R24490)*('Raw Data'!$W:$W="e")),T24488:T24489)))))),"")
S24493	S24493	=ARRAYTOTEXT(SORT(UNIQUE(FILTER($V:$V,($U:$U=R24490)*($W:$W="r")))))
T24493	T24493	=ARRAYTOTEXT(SORT(UNIQUE(FILTER($V:$V,($U:$U=S24490)*($W:$W="E")))))

Oh wow, thank you for your formula
Although, is it supposed to work as an array formula?

Regarding getting rid of the whole column references in the last two formulae, I'm currently pointing to it like that because the column has either R or E values (representing revenue or expenses).
I'm trying to extract unique codes for either revenue or expense (while excluding specific codes).
For example, for this shipment, the revenue codes are: "Wheel, Tyre, Rims". And if we exclude Wheel, then the output should is: "Rims, Tyre"

As for the other formula, that is just producing all the codes for either revenue or expense for each shipment.

The table of data has many rows and columns, this is just a reduced version for reference.

 

[Fluff]

You should never use entire columns when using arrays, limit the range to something more suitable like

=ARRAYTOTEXT(SORT(UNIQUE(FILTER($V2:$V50000,($U2:$U50000=R24490)*($W2:$W50000="r")))))

 

[danhendo888]

You should never use entire columns when using arrays, limit the range to something more suitable like

=ARRAYTOTEXT(SORT(UNIQUE(FILTER($V2:$V50000,($U2:$U50000=R24490)*($W2:$W50000="r")))))

Oh I see, noted, will do.

For your SUMIFS/TOROW formula, I've put it in cell S24480, is it supposed to work as an array formula so that the results spill down? Or should I use each row (e.g. R24480) as the search value?
Also, would one be faster than the other, or both would operate at the same speed?

 

[Fluff]

No it's not meant to spill, it will do exactly what the formula you posted does.