Substituting Filter function in Excel 2016

[Pbobas]

Hi, I'm trying to replace a FILTER function on this Excel file which run on Excel 365 to an older version of Excel 2016. The goal is to list down products to be sourced to potential customers, either based on quantity (sorted either highest to lowest or vice versa) or by distance (nearest to furthest or vice versa). Tried to browse through several threads with no luck and given my limited Excel competency, I'm hoping anyone can be of assistance to shed some light on this. Any ideas are greatly appreciated.

DATA_BARU_TKI.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2	1	2	3	4			Base	Distance
3	Product	Qty	Customer	Distance			Column No.	4
4	CR3Y98957	4	PT A	10			Sort	Descend	-1
5	CR3Y98963	7	PT BRM	15
6	CR3Y98957	8	PT C	6			Product	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5
7	CR3Y98957	10	PT D	4			CR3Y98957	PT A	PT ASDQWE	PT B	PT C	PT D
8	CR3Y98958	6	PT X	10			CR3Y98963	PT MIB	PT BRM
9	CR3Y98958	12	PT Y	2			CR3Y98958	PT X	PT Y	PT Z
10	CR3Y98958	7	PT Z	1			CR3Y98960	PT ABC	PT DEF	PT GHI	PT RST	PT TUV
11	CR3Y98960	20	PT ABC	50			CR3Y98962	PT ASDQWE
12	CR3Y98960	15	PT DEF	40
13	CR3Y98960	13	PT GHI	30
14	CR3Y98960	12	PT RST	20
15	CR3Y98960	10	PT TUV	10
16	CR3Y98962	100	PT ASDQWE	50
17	CR3Y98957	6	PT B	8
18	CR3Y98963	8	PT MIB	20
19	CR3Y98957	20	PT ASDQWE	10
Test

Cell Formulas
Range	Formula
B2:D2	B2	=A2+1
H3	H3	=IF(H2="Distance",$D$2,$B$2)
I4	I4	=IF(H4="Ascend",1,-1)
G7:G11	G7	=UNIQUE(FILTER(A4:A96,A4:A96<>""))
H7:L7,H12:CG13,H11,H10:L10,H9:J9,H8:I8	H7	=IF(IFNA(TRANSPOSE(INDEX(SORT(SORTBY(FILTER($A$4:$D$97,$A$4:$A$97=$G7), INDEX(FILTER($A$4:$D$97,$A$4:$A$97=$G7),0,3),1, INDEX(FILTER($A$4:$D$97,$A$4:$A$97=$G7),0,2),1),$H$3,$I$4),0,3)),"Check data")=0,"",IFNA(TRANSPOSE(INDEX(SORT(SORTBY(FILTER($A$4:$D$97,$A$4:$A$97=$G7), INDEX(FILTER($A$4:$D$97,$A$4:$A$97=$G7),0,3),1, INDEX(FILTER($A$4:$D$97,$A$4:$A$97=$G7),0,2),1),$H$3,$I$4),0,3)),"Check data"))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=Reference!$A$4:$A$5
H4	List	=Reference!$B$4:$B$5

 

[AhoyNC]

Try:
Instead of the formula in F4 ( you can drag this formula down as needed) to get the unique products, I'd just use the REMOVE DUPLICATES option under the data ribbon.
You can just drag the formula in G4 down and across as needed.
This is an array formula and you may need to enter it with CTRL-SHIFT-ENTER. I don't think you will have to though, since I used the AGGREGATE function.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	2	3	4
3	Product	Qty	Customer	Distance		Product	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5
4	CR3Y98957	4	PT A	10		CR3Y98957	PT A	PT ASDQWE	PT B	PT C	PT D
5	CR3Y98963	7	PT BRM	15		CR3Y98963	PT MIB	PT BRM
6	CR3Y98957	8	PT C	6		CR3Y98958	PT X	PT Y	PT Z
7	CR3Y98957	10	PT D	4		CR3Y98960	PT ABC	PT DEF	PT GHI	PT RST	PT TUV
8	CR3Y98958	6	PT X	10		CR3Y98962	PT ASDQWE
9	CR3Y98958	12	PT Y	2
10	CR3Y98958	7	PT Z	1
11	CR3Y98960	20	PT ABC	50
12	CR3Y98960	15	PT DEF	40
13	CR3Y98960	13	PT GHI	30
14	CR3Y98960	12	PT RST	20
15	CR3Y98960	10	PT TUV	10
16	CR3Y98962	100	PT ASDQWE	50
17	CR3Y98957	6	PT B	8
18	CR3Y98963	8	PT MIB	20
19	CR3Y98957	20	PT ASDQWE	10
Sheet1

Cell Formulas
Range	Formula
B2:D2	B2	=A2+1
F4:F8	F4	=IFERROR(INDEX($A$4:$A$19,SMALL(IF(FREQUENCY(MATCH($A$4:$A$19,$A$4:$A$19,0),ROW($A$4:$A$19)-ROW($A$4)+1),ROW($A$4:$A$19)-ROW($A$4)+1),ROWS($F$4:F4))),"")
G4:K8	G4	=IFERROR(INDEX($C$4:$C$19,AGGREGATE(15,6,(ROW($C$4:$C$19)-ROW($C$4)+1)/($A$4:$A$19&"\"&$D$4:$D$19=$F4&"\"&AGGREGATE(14,6,IF($A$4:$A$19=$F4,$D$4:$D$19),COLUMNS($G$31:G31))),COUNTIF($G25:G25,G25))),"")

 

[Pbobas]

Awesome, thanks for the idea. It worked wonders on the Product column, however when I tried to reproduce the solution to sort the customers, it returned blank cells instead as per below screenshot. Curious about the COLUMNS and COUNTIF function though, can you elaborate what does those 2 functions do here? Since they all seem to refer to blank cells.

DATA_BARU_TKI_testbaru.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	2	3	4		Base	Quantity
3	Product	Qty	Customer	Distance		Column No.	2
4	CR3Y98957	4	PT A	10		Sort	Ascend	1
5	CR3Y98963	7	PT BRM	15
6	CR3Y98957	8	PT C	6		Product	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5
7	CR3Y98957	10	PT D	4		CR3Y98957
8	CR3Y98958	6	PT X	10		CR3Y98963
9	CR3Y98958	12	PT Y	2		CR3Y98958
10	CR3Y98958	7	PT Z	1		CR3Y98960
11	CR3Y98960	20	PT ABC	50		CR3Y98962
12	CR3Y98960	15	PT DEF	40
13	CR3Y98960	13	PT GHI	30
14	CR3Y98960	12	PT RST	20
15	CR3Y98960	10	PT TUV	10
16	CR3Y98962	100	PT ASDQWE	50
17	CR3Y98957	6	PT B	8
18	CR3Y98963	8	PT MIB	20
19	CR3Y98957	20	PT ASDQWE	8
Test 2021 keatas

Cell Formulas
Range	Formula
B2:D2	B2	=A2+1
G3	G3	=IF(G2="Distance",$D$2,$B$2)
H4	H4	=IF(G4="Ascend",1,-1)
G7:K11	H7	=IFERROR(INDEX($C$4:$C$19,AGGREGATE(15,6,(ROW($C$4:$C$19)-ROW($C$4)+1)/($A$4:$A$19&"\"&$D$4:$D$19=$F7&"\"&AGGREGATE(14,6,IF($A$4:$A$19=$F7,$D$4:$D$19),COLUMNS($E$31:F31))),COUNTIF($E25:F25,F25))),"")
F7:F12	F7	=IFERROR(INDEX($A$4:$A$19,SMALL(IF(FREQUENCY(MATCH($A$4:$A$19,$A$4:$A$19,0),ROW($A$4:$A$19)-ROW($A$4)+1),ROW($A$4:$A$19)-ROW($A$4)+1),ROWS($F$7:F7))),"")
G12:CF13	G12	=IF(IFNA(TRANSPOSE(INDEX(SORT(SORTBY(FILTER($A$4:$D$97,$A$4:$A$97=$F12), INDEX(FILTER($A$4:$D$97,$A$4:$A$97=$F12),0,3),1, INDEX(FILTER($A$4:$D$97,$A$4:$A$97=$F12),0,2),1),$G$3,$H$4),0,3)),"Check data")=0,"",IFNA(TRANSPOSE(INDEX(SORT(SORTBY(FILTER($A$4:$D$97,$A$4:$A$97=$F12), INDEX(FILTER($A$4:$D$97,$A$4:$A$97=$F12),0,3),1, INDEX(FILTER($A$4:$D$97,$A$4:$A$97=$F12),0,2),1),$G$3,$H$4),0,3)),"Check data"))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=Reference!$A$4:$A$5
G4	List	=Reference!$B$4:$B$5

 

[AhoyNC]

The formula I gave above isn't going to work.
The COUNTIF and COLUMNS functions are used as number increments for the LARGE function.
In the case of distance you have two customers for product CR3Y98957 that are at a distance of10. So in cell G4 the COUNTIF returns 1 for the first match and in cell H4 the COUNTIF returns a 2 for the second match in the table.

I had to add a helper matrix to get the formulas to work.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	2	3	4		Distance
3	Product	Qty	Customer	Distance		Product	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5
4	CR3Y98957	4	PT A	10		CR3Y98957	PT ASDQWE	PT A	PT B	PT C	PT D
5	CR3Y98963	7	PT BRM	15		CR3Y98963	PT MIB	PT BRM
6	CR3Y98957	8	PT C	6		CR3Y98958	PT X	PT Y	PT Z
7	CR3Y98957	10	PT D	4		CR3Y98960	PT ABC	PT DEF	PT GHI	PT RST	PT TUV
8	CR3Y98958	6	PT X	10		CR3Y98962	PT ASDQWE
9	CR3Y98958	12	PT Y	2
10	CR3Y98958	7	PT Z	1		Helper Matrix for Distance
11	CR3Y98960	20	PT ABC	50		CR3Y98957	10	10	8	6	4
12	CR3Y98960	15	PT DEF	40		CR3Y98963	20	15
13	CR3Y98960	13	PT GHI	30		CR3Y98958	10	2	1
14	CR3Y98960	12	PT RST	20		CR3Y98960	50	40	30	20	10
15	CR3Y98960	10	PT TUV	10		CR3Y98962	50
16	CR3Y98962	100	PT ASDQWE	50
17	CR3Y98957	6	PT B	8
18	CR3Y98963	8	PT MIB	20		Qty	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5
19	CR3Y98957	20	PT ASDQWE	10		CR3Y98957	PT ASDQWE	PT D	PT C	PT B	PT A
20						CR3Y98963	PT MIB	PT BRM
21						CR3Y98958	PT Y	PT Z	PT X
22						CR3Y98960	PT ABC	PT DEF	PT GHI	PT RST	PT TUV
23						CR3Y98962	PT ASDQWE
24
25						Helper Matrix for Qty
26						CR3Y98957	20	10	8	6	4
27						CR3Y98963	8	7
28						CR3Y98958	12	7	6
29						CR3Y98960	20	15	13	12	10
30						CR3Y98962	100
Sheet1

Cell Formulas
Range	Formula
B2:D2	B2	=A2+1
F4:F8	F4	=IFERROR(INDEX($A$4:$A$19,SMALL(IF(FREQUENCY(MATCH($A$4:$A$19,$A$4:$A$19,0),ROW($A$4:$A$19)-ROW($A$4)+1),ROW($A$4:$A$19)-ROW($A$4)+1),ROWS($F$4:F4))),"")
G4:K8	G4	=IF(G11="","",(INDEX($C$4:$C$19,AGGREGATE(14,6,(ROW($C$4:$C$19)-ROW($C$4)+1)/($A$4:$A$19&"\"&$D$4:$D$19=$F4&"\"&G11),COUNTIF($G11:G11,G11)))))
G11:K15	G11	=IFERROR(AGGREGATE(14,6,IF($A$4:$A$19=$F4,$D$4:$D$19),COLUMNS($G$1:G1)),"")
G19:K23	G19	=IF(G26="","",(INDEX($C$4:$C$19,AGGREGATE(14,6,(ROW($C$4:$C$19)-ROW($C$4)+1)/($A$4:$A$19&"\"&$B$4:$B$19=$F4&"\"&G26),COUNTIF($G26:G26,G26)))))
G26:K30	G26	=IFERROR(AGGREGATE(14,6,IF($A$4:$A$19=$F4,$B$4:$B$19),COLUMNS($G$1:G1)),"")

 

[Pbobas]

Awesome, that last piece totally clears things up. Thank you again for the help

 

[AhoyNC]

You're welcome. Thanks for the feedback.