Filter Alternative

[JamesRo]

Hi.

I am looking for an alternative way of achieving the following filter for my colleagues who have older Excel version.

=UNIQUE(FILTER(B5:B51,A5:A51='Quote Form'!B1))

Help appreciated, TIA

James.

 

[JamesCanale]

Recommend they upgrade:

MrExcelPlayground19.xlsx
	A	B	C	D	E	F
1		Alpha
2
3			24
4	Thing	Stuff	Helper1	Answer		365
5	Alpha	A1	1	A1		A1
6	Beta	A2	99999	A3		A3
7	Alpha	A3	3	A5		A5
8	Beta	A4	99999	A6		A6
9	Alpha	A5	5	A7		A7
10	Alpha	A6	6	A9		A9
11	Alpha	A7	7	A16		A16
12	Beta	A8	99999	A17		A17
13	Alpha	A9	9	A18		A18
14	Beta	A10	99999	A19		A19
15	Beta	A11	99999	A20		A20
16	Beta	A12	99999	A21		A21
17	Beta	A13	99999	A25		A25
18	Beta	A14	99999	A27		A27
19	Beta	A15	99999	A29		A29
20	Alpha	A16	16	A31		A31
21	Alpha	A17	17	A33		A33
22	Alpha	A18	18	A35		A35
23	Alpha	A19	19	A37		A37
24	Alpha	A20	20	A39		A39
25	Alpha	A21	21	A41		A41
26	Beta	A22	99999	A43		A43
27	Beta	A23	99999	A45		A45
28	Beta	A24	99999	A47		A47
29	Alpha	A25	25
30	Beta	A26	99999
31	Alpha	A27	27
32	Beta	A28	99999
33	Alpha	A29	29
34	Beta	A30	99999
35	Alpha	A31	31
36	Beta	A32	99999
37	Alpha	A33	33
38	Beta	A34	99999
39	Alpha	A35	35
40	Beta	A36	99999
41	Alpha	A37	37
42	Beta	A38	99999
43	Alpha	A39	39
44	Beta	A40	99999
45	Alpha	A41	41
46	Beta	A42	99999
47	Alpha	A43	43
48	Beta	A44	99999
49	Alpha	A45	45
50	Beta	A46	99999
51	Alpha	A47	47
Sheet31

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(C5:C51<99999))
D5:D28	D5	=INDEX(B5:B51,SMALL(C5:C51,ROW(INDIRECT("1:"&C3))))
F5:F28	F5	=UNIQUE(FILTER(B5:B51,A5:A51=B1))
C5:C51	C5	=IF(A5=$B$1,ROW()-4,99999)
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D
1		Staffordshire
2
3
4
5	North Yorkshire	Richmondshire		Cannock Chase
6	Hertfordshire	Dacorum		Stoke-on-Trent
7	West Yorkshire	Leeds		Newcastle-under-Lyme
8	North Yorkshire	Harrogate		Staffordshire Moorlands
9	County Durham	County Durham
10	Devon	West Devon
11	West Midlands	Birmingham
12	West Yorkshire	Bradford
13	Surrey	Tandridge
14	North Yorkshire	Craven
15	Derbyshire	Derbyshire Dales
16	South Yorkshire	Sheffield
17	West Yorkshire	Kirklees
18	Surrey	Mole Valley
19	Staffordshire	Cannock Chase
20	West Yorkshire	Bradford
21	County Durham	County Durham
22	Lancashire	Pendle
23	County Durham	County Durham
24	Dorset	West Dorset
25	South Yorkshire	Sheffield
26	Somerset	Taunton Deane
27	West Midlands	Birmingham
28	Gloucestershire	Forest of Dean
29	South Yorkshire	Sheffield
30	Greater Manchester	Tameside
31	West Yorkshire	Bradford
32	Lancashire	Rossendale
33	South Yorkshire	Barnsley
34	Cornwall	Cornwall
35	Hampshire	East Hampshire
36	Lancashire	Hyndburn
37	Derbyshire	High Peak
38	Northumberland	Northumberland
39	West Midlands	Sandwell
40	Staffordshire	Stoke-on-Trent
41	West Yorkshire	Kirklees
42	Greater Manchester	Tameside
43	Staffordshire	Stoke-on-Trent
44	West Midlands	Dudley
45	Devon	West Devon
46	Staffordshire	Newcastle-under-Lyme
47	Staffordshire	Staffordshire Moorlands
48	Herefordshire	Herefordshire, County of
49	West Yorkshire	Bradford
50	Derbyshire	High Peak
51	West Midlands	Dudley
Sheet7

Cell Formulas
Range	Formula
D5:D10	D5	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$5:$B$51)/($A$5:$A$51=$B$1)/(ISNA(MATCH($B$5:$B$51,D$4:D4,0))),1)),"")

 

[gargamalebarbosa]

Hi ,
You can try this array formula.

IFERROR(INDEX($B$5:$B$14,MATCH(0,IF($G$3=$C$5:$C$14,COUNTIF($D$4:$D4,$B$5:$B$14). ""),0)),"")

 

[Fluff]

That formula will require array entry, also it would be helpful to actually post the formula, rather than just an image.

 

[JamesRo]

Recommend they upgrade:

MrExcelPlayground19.xlsx
	A	B	C	D	E	F
1		Alpha
2
3			24
4	Thing	Stuff	Helper1	Answer		365
5	Alpha	A1	1	A1		A1
6	Beta	A2	99999	A3		A3
7	Alpha	A3	3	A5		A5
8	Beta	A4	99999	A6		A6
9	Alpha	A5	5	A7		A7
10	Alpha	A6	6	A9		A9
11	Alpha	A7	7	A16		A16
12	Beta	A8	99999	A17		A17
13	Alpha	A9	9	A18		A18
14	Beta	A10	99999	A19		A19
15	Beta	A11	99999	A20		A20
16	Beta	A12	99999	A21		A21
17	Beta	A13	99999	A25		A25
18	Beta	A14	99999	A27		A27
19	Beta	A15	99999	A29		A29
20	Alpha	A16	16	A31		A31
21	Alpha	A17	17	A33		A33
22	Alpha	A18	18	A35		A35
23	Alpha	A19	19	A37		A37
24	Alpha	A20	20	A39		A39
25	Alpha	A21	21	A41		A41
26	Beta	A22	99999	A43		A43
27	Beta	A23	99999	A45		A45
28	Beta	A24	99999	A47		A47
29	Alpha	A25	25
30	Beta	A26	99999
31	Alpha	A27	27
32	Beta	A28	99999
33	Alpha	A29	29
34	Beta	A30	99999
35	Alpha	A31	31
36	Beta	A32	99999
37	Alpha	A33	33
38	Beta	A34	99999
39	Alpha	A35	35
40	Beta	A36	99999
41	Alpha	A37	37
42	Beta	A38	99999
43	Alpha	A39	39
44	Beta	A40	99999
45	Alpha	A41	41
46	Beta	A42	99999
47	Alpha	A43	43
48	Beta	A44	99999
49	Alpha	A45	45
50	Beta	A46	99999
51	Alpha	A47	47
Sheet31

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(C5:C51<99999))
D5:D28	D5	=INDEX(B5:B51,SMALL(C5:C51,ROW(INDIRECT("1:"&C3))))
F5:F28	F5	=UNIQUE(FILTER(B5:B51,A5:A51=B1))
C5:C51	C5	=IF(A5=$B$1,ROW()-4,99999)
Dynamic array formulas.

Thanks for the various replies. This solution is working best for me, I could not adapt the others to my sheet so easily.

 

[Fluff]

This solution is working best for me

Except it does not return distinct values only (as seen below) & it will not work in earlier versions as they do not have spill ranges.

Fluff.xlsm
	A	B	C	D	E	F
1		Alpha
2
3			24
4	Thing	Stuff	Helper1	Answer		365
5	Alpha	A1	1	A1		A1
6	Beta	A2	99999	A3		A3
7	Alpha	A3	3	A5		A5
8	Beta	A4	99999	A5		A9
9	Alpha	A5	5	A5		A16
10	Alpha	A5	6	A9		A17
11	Alpha	A5	7	A16		A18
12	Beta	A8	99999	A17		A19
13	Alpha	A9	9	A18		A20
14	Beta	A10	99999	A19		A21
15	Beta	A11	99999	A20		A25
16	Beta	A12	99999	A21		A27
17	Beta	A13	99999	A25		A29
18	Beta	A14	99999	A27		A31
19	Beta	A15	99999	A29		A33
20	Alpha	A16	16	A31		A35
21	Alpha	A17	17	A33		A37
22	Alpha	A18	18	A35		A39
23	Alpha	A19	19	A37		A41
24	Alpha	A20	20	A39		A43
25	Alpha	A21	21	A41		A45
26	Beta	A22	99999	A43		A47
27	Beta	A23	99999	A45
28	Beta	A24	99999	A47
29	Alpha	A25	25
30	Beta	A26	99999
31	Alpha	A27	27
32	Beta	A28	99999
33	Alpha	A29	29
34	Beta	A30	99999
35	Alpha	A31	31
36	Beta	A32	99999
37	Alpha	A33	33
38	Beta	A34	99999
39	Alpha	A35	35
40	Beta	A36	99999
41	Alpha	A37	37
42	Beta	A38	99999
43	Alpha	A39	39
44	Beta	A40	99999
45	Alpha	A41	41
46	Beta	A42	99999
47	Alpha	A43	43
48	Beta	A44	99999
49	Alpha	A45	45
50	Beta	A46	99999
51	Alpha	A47	47
Sheet5

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(C5:C51<99999))
D5:D28	D5	=INDEX(B5:B51,SMALL(C5:C51,ROW(INDIRECT("1:"&C3))))
F5:F26	F5	=UNIQUE(FILTER(B5:B51,A5:A51=B1))
C5:C51	C5	=IF(A5=$B$1,ROW()-4,99999)
Dynamic array formulas.

 

[JamesRo]

Except it does not return distinct values only (as seen below) & it will not work in earlier versions as they do not have spill ranges.

Fluff.xlsm
	A	B	C	D	E	F
1		Alpha
2
3			24
4	Thing	Stuff	Helper1	Answer		365
5	Alpha	A1	1	A1		A1
6	Beta	A2	99999	A3		A3
7	Alpha	A3	3	A5		A5
8	Beta	A4	99999	A5		A9
9	Alpha	A5	5	A5		A16
10	Alpha	A5	6	A9		A17
11	Alpha	A5	7	A16		A18
12	Beta	A8	99999	A17		A19
13	Alpha	A9	9	A18		A20
14	Beta	A10	99999	A19		A21
15	Beta	A11	99999	A20		A25
16	Beta	A12	99999	A21		A27
17	Beta	A13	99999	A25		A29
18	Beta	A14	99999	A27		A31
19	Beta	A15	99999	A29		A33
20	Alpha	A16	16	A31		A35
21	Alpha	A17	17	A33		A37
22	Alpha	A18	18	A35		A39
23	Alpha	A19	19	A37		A41
24	Alpha	A20	20	A39		A43
25	Alpha	A21	21	A41		A45
26	Beta	A22	99999	A43		A47
27	Beta	A23	99999	A45
28	Beta	A24	99999	A47
29	Alpha	A25	25
30	Beta	A26	99999
31	Alpha	A27	27
32	Beta	A28	99999
33	Alpha	A29	29
34	Beta	A30	99999
35	Alpha	A31	31
36	Beta	A32	99999
37	Alpha	A33	33
38	Beta	A34	99999
39	Alpha	A35	35
40	Beta	A36	99999
41	Alpha	A37	37
42	Beta	A38	99999
43	Alpha	A39	39
44	Beta	A40	99999
45	Alpha	A41	41
46	Beta	A42	99999
47	Alpha	A43	43
48	Beta	A44	99999
49	Alpha	A45	45
50	Beta	A46	99999
51	Alpha	A47	47
Sheet5

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(C5:C51<99999))
D5:D28	D5	=INDEX(B5:B51,SMALL(C5:C51,ROW(INDIRECT("1:"&C3))))
F5:F26	F5	=UNIQUE(FILTER(B5:B51,A5:A51=B1))
C5:C51	C5	=IF(A5=$B$1,ROW()-4,99999)
Dynamic array formulas.

Well that could be a problem!
I found your solution would only show the first item.
Have not figured out why as yet:

Topline Quoting.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
5	Annealed	4mm Clear	£26.76	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		4.00	4mm Silver
6	Annealed	6mm Clear	£34.41	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		6.00
7	Annealed	8mm Clear	£41.40	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		8.00
8	Annealed	10mm Clear	£43.36	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		10.00
9	Annealed	12mm Clear	£81.50	0.00	POA	POA	1.25	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	POA	POA	POA	POA		12.00
10	Annealed	15mm Clear	£107.11	0.00	POA	POA	1.25	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	POA	POA	POA	POA		15.00
11	Annealed	4mm Optiwhite	£48.31	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		4.00
12	Annealed	6mm Optiwhite	£59.21	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		6.00
13	Annealed	8mm Optiwhite	£69.03	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		8.00
14	Annealed	10mm Optiwhite	£103.52	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		10.00
15	Annealed	12mm Optiwhite	£131.46	0.00	POA	POA	1.25	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	POA	POA	POA	POA		12.00
16	Annealed	15mm Optiwhite	£197.19	0.00	POA	POA	1.25	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	POA	POA	POA	POA		15.00
17	Annealed	4mm Satin	£47.01	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		4.00
18	Annealed	6mm Satin	£101.84	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		6.00
19	Annealed	8mm Satin	£102.34	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		8.00
20	Annealed	10mm Satin	£102.83	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		10.00
21	Annealed	12mm Satin	£131.46	0.00	POA	POA	1.25	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	POA	POA	POA	POA		12.00
22	Annealed	6mm Bronze/Grey	£70.18	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		6.00
23	Annealed	8mm Bronze/Grey	£92.75	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		8.00
24	Annealed	10mm Bronze/Grey	£115.31	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		10.00
25	Annealed	8mm Reeded	£111.09	0.00	8.25	12.00	1.00	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		8.00
26	Laminated	7.2mm Pyroguard	£165.00	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	25.00	100.00	N/A	N/A	N/A	N/A		7.20
27	Mirror	4mm Silver	£34.73	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	8.25	8.25	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		4.00
28	Mirror	6mm Silver	£37.90	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	8.25	8.25	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		6.00
29	Mirror	6mm Bronze/Grey Silver	£62.62	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	8.25	8.25	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		6.00
30	Mirror	6mm Antique Sil/Brz/Gry	£120.00	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	8.25	8.25	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		6.00
31	Toughened	4mm Clear	£28.89	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		4.00
32	Toughened	6mm Clear	£35.01	0.00	8.25	12.00	1.00	1.25	2.00	2.00	0.25	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		6.00
33	Toughened	8mm Clear	£46.91	0.00	8.25	12.00	N/A	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		8.00
34	Toughened	10mm Clear	£49.13	0.00	8.25	12.00	N/A	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	7.50	20.00	7.50	10.00		10.00
35	Toughened	12mm Clear	£94.51	0.00	POA	POA	N/A	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	POA	POA	POA	POA		12.00
36	Toughened	15mm Clear	£158.90	0.00	POA	POA	N/A	1.25	2.00	3.25	0.50	N/A	N/A	65.00	30.00	20.00	25.00	100.00	POA	POA	POA	POA		15.00
Data

Cell Formulas
Range	Formula
Y5	Y5	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$5:$B$51)/($A$5:$A$51='Quote Form'!B1)/(ISNA(MATCH($B$5:$B$51,Y$4:Y4,0))),1)),"")

Option dropdown on 'Quote Form' is set to "Mirror".

 

[Fluff]

You need to anchor the B1 & then drag the formula down

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$5:$B$51)/($A$5:$A$51='Quote Form'!B$1)/(ISNA(MATCH($B$5:$B$51,Y$4:Y4,0))),1)),"")

 

[JamesRo]

You need to anchor the B1 & then drag the formula down

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$5:$B$51)/($A$5:$A$51='Quote Form'!B$1)/(ISNA(MATCH($B$5:$B$51,Y$4:Y4,0))),1)),"")

Oops, thought I did and was getting blank cells.

Can confirm this is working, thanks!
James.