Optimization and Filtering

[dsdevon06]

I have two different data sets with similar data but different model numbers. I want to find a discus model that is closest to the reed model based on a certain criteria. That is: Uses the same refrigerant, has the same application, and then the closest capacity. I want this to be robust enough that if the criteria can change. The person who set up this excel previously did not do this. Then once this model is found, it needs outputted in the table to the right with specific data correlating to that discus model.

This was the formula used previously that works but is flawed in its filtering and picking.

=IFERROR(INDEX(A$21:A$25,MATCH(MIN(IF(($E$21:$E$25=$E9)*($C$21:$C$25=$C9)*($I$21:$I$25=$I9)*($J$1=$J$21:$J$25),ABS($G9-$G$21:$G$25))),IF(($E$21:$E$25=$E9)*($J$1=$J$21:$J$25)*($C$21:$C$25=$C9)*($I$21:$I$25=$I9),ABS($G9-$G$21:$G$25)),0)),"-")

Columns M through U and V have similar equations that look through the data set. I tried a pivot table but it gets screwed up with not having exact answers for example, Capacity.

 

[Fluff]

That is still asking me to sign in.

 

[Fluff]

Scrub that, I'm in.

 

[Fluff]

Ok, for the 1st 2 columns, how about

Book.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1										standard
2		All N/A, hard code
3
4	Reed Models Listed out
5
6	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 1	Column 2
7	1AA1-1111	N/A	AA1	R-11	A	10	15000	Reed	50	-	-
8	1AA1-1111	N/A	AA2	R-11	B	10	17000	Reed	60	1AAAA11AA	1DAAA11AA
9	2BB2-2222	N/A	BB2	R-00	C	20	18000	Reed	60	3CCCCC11CC	3DCCCC11CC
10	2BB2-2222	N/A	AA3	R-01	D	20	20000	Reed	60	-	-
11										-	-
12
13
14
15
16
17
18	Reed Models Listed out
19
20	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Unloader Type
21	1AAAA11AA	1DAAA11AA	AA1	R-11	A	11	16000	Discus	60	Standard
22	1AAAA11AA	1DAAA11AA	AA2	R-11	B	11	18000	Discus	60	Standard
23	2BBBBB11BB	2DBBBB11BB	BB2	R-00	C	21	21000	Discus	60	Standard
24	2BBBBB11BB	2DBBBB11BB	AA1	R-01	D	21	17500	Discus	60	2 Bank Unloader
25	3CCCCC11CC	3DCCCC11CC	bb2	R-00	C	31	18100	Discus	60	Standard
Sheet1

Cell Formulas
Range	Formula
J7:K11	J7	=LET(f,FILTER(A$21:G$25,($E$21:$E$25=$E7)*($C$21:$C$25=$C7)*($I$21:$I$25=$I7)*($J$1=$J$21:$J$25)),IFERROR(INDEX(SORTBY(f,ABS(G7-INDEX(f,,7))),1,{1,2}),"-"))
Dynamic array formulas.

 

[dsdevon06]

I just realized that when I told you that the criteria list I wanted that I changed the column names so you don't know.

For cell A7 I want to have the A20:J27 searched and output the value in Column 1 into J7.

The criteria is: Column 4, Column 5, and then closest to Column 7

 

[Fluff]

So you don't to check columns 3, 9 & 10?

 

[dsdevon06]

Correct. The values in the other columns do not matter at this moment.

If filtering first column 4 and then column 5; then if there are still multiple data points then closest to Column 7 will narrow it to one value that can be outputted into J7

 

[Fluff]

Ok, how about

Book.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1										standard
2		All N/A, hard code
3
4	Reed Models Listed out
5
6	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 1	Column 2
7	1AA1-1111	N/A	AA1	R-11	A	10	15000	Reed	50	1AAAA11AA	1DAAA11AA
8	1AA1-1111	N/A	AA2	R-11	B	10	17000	Reed	60	1AAAA11AA	1DAAA11AA
9	2BB2-2222	N/A	BB2	R-00	C	20	18000	Reed	60	3CCCCC11CC	3DCCCC11CC
10	2BB2-2222	N/A	AA3	R-01	D	20	20000	Reed	60	2BBBBB11BB	2DBBBB11BB
11										-	-
12
13
14
15
16
17
18	Reed Models Listed out
19
20	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Unloader Type
21	1AAAA11AA	1DAAA11AA	AA1	R-11	A	11	16000	Discus	60	Standard
22	1AAAA11AA	1DAAA11AA	AA2	R-11	B	11	18000	Discus	60	Standard
23	2BBBBB11BB	2DBBBB11BB	BB2	R-00	C	21	21000	Discus	60	Standard
24	2BBBBB11BB	2DBBBB11BB	AA1	R-01	D	21	17500	Discus	60	2 Bank Unloader
25	3CCCCC11CC	3DCCCC11CC	bb2	R-00	C	31	18100	Discus	60	Standard
Sheet1

Cell Formulas
Range	Formula
J7:K11	J7	=LET(f,FILTER(A$21:G$25,($E$21:$E$25=$E7)*($D$21:$D$25=$D7)),IFERROR(INDEX(SORTBY(f,ABS(G7-INDEX(f,,7))),1,{1,2}),"-"))
Dynamic array formulas.

 

[dsdevon06]

It looks like it is working from this end. Only way for me to completely know is to throw it into my large data set.

In the blue section. I would also like it to pull all of the data for that row and put it with it to the right.

It appears I am having a #SPILL! error in the cell J7. I expanded A21:G25 to encompass the entire data set. Then Moved D to take the whole row same with G.

I get the value in the data set in column D by: =MID(J8,4,LEN(J8)-6)
Column E by: =LEFT(RIGHT(J10,2),1)

*** NOTICED: Are you also having the value go by which value in the data set is closest in column 7. Otherwise it will be trying to output many different values*******

 

[Fluff]

Are you also having the value go by which value in the data set is closest in column 7

Yes. That's what the sortby is doing.

I am having a #SPILL! error in the cell J7

Clear all cells to the right of the formula

 

[dsdevon06]

It successfully outputted data. But out of my 57 rows it only outputted 4 rows with values. The rest were "-"

That could mean though that it really doesnt exist. ** Checking via Pivot table**