advanced and complex "fuzzy" filter on multiple criteria

[Ebru]

Hi Excel Guru's

I do not know how to get following problem solved by using the exact xls formula:

We have customers which can configure their goods. Most configurations are already available, but the customer can request a new configuration of the goods.
The easiest way for me is to lookup *the most similar existing product* for their configuration.

And here comes the problem:
let's say we have a product configured as
variable 1 = characteristic A
variable 2 = characteristic B
variable 3 = characteristic C
variable 4 = characteristic D

and another one as
variable 1 = characteristic E
variable 2 = characteristic F
variable 3 = characteristic G
variable 4 = characteristic H

and so on.

When I get a new customer request as
variable 1 = characteristic E
variable 2 = characteristic B
variable 3 = characteristic C
variable 4 = characteristic D
and I start filtering the usual way on eg variable 1 = E, then it looks as if the other characteristics are not available, only F, G, H.
But I want to find the closest available product configuration to the new need of a customer (thus A, B, C, D).

So how can a filter in xls be defined which does this?

Ebru

[loginjmor]

Hi -

Can you provide a small spreadsheet with some sample data and an example or two of the outcomes you want?

[Ebru]

An attached was made -I think it must be a formula with correlation I guess now.

In the example below you see the "new requirement" (with 5 specifications)
below you see the configuration of the already ordered material.
I want to see that the "produced product 1" is the closest to the "new requirement".
This allows me to identify the proven production process which is closest to the new requirement.

When I filter on E for variable 1, I immediately exclude the 1st line. So Excel must take cells on line B2
and compare them with the 5 lines below. and indicate the degree of similarity per produced product vs new requirement.

I really hope someone can help me because this would be so useful on an extremely large dataset.

Ebru.


123.jpg

[shg]

Please attach a workbook, not a picture.

[loginjmor]

Hi -

Use SUMPRODUCT to count how many criteria each product matches the specification. Then use INDEX/MATCH functions to list the product with the highest total from the SUMPRODUCT formula (closest to the specification).

Put this formula in H5 and copy down to H9:

=SUMPRODUCT((B5:F5=$B$3:$F$3)*1)

Put this formula in I5:

=INDEX(A5:A9,MATCH(MAX(H5:H9),H5:H9,0))

Hope this helps.

[FlameRetired]

Try array entering this in G2 and fill down.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then filter on G for the maximum value.

Row\Col	A	B	C	D	E	F	G	H
2		Variable 1	Variable 2	Variable 3	Variable 4	Variable 5
3	new requirement	E	B	C	D	E
4
5	produced product 1	A	B	C	D	E	4	Array enter this in G2 and fill down:
6	produced product 2	E	G	H	I	J	1	=SUM(IF(ISNA(MATCH(B5:F5,$B$3:$F$3,0)),0,1))
7	produced product 3	Z	W	R	V	T	0
8	produced product 4	P	L	M	N	F	0
9	produced product 5	S	P	I	E	T	1

[Ebru]

Hi there!
Thanks so much
I used =SUM(IF(ISNA(MATCH(B5:F5,$B$3:$F$3,0)),0,1)) and it works :-)
It is a big help, but I think it can be improved.

This formula looks if the data is in one of the cells in the row is the same in another row.
But it should be delimited to columns search:
Sometimes the same specifications re-occur but on another variable. Eg "red color" is used for the tissue
sometimes for the buttons. The formula above does not detect this.
So I think this formula should be delimited to one cell of row $B$3:$F$3 and then make the sum of
all cells in the row to get the most matching produced product.
Could you provide an example formula for this?

It would also be very convenient if the cells in the database get a color code for the ones which do not match the formula, then we can
see straight away were the difference is.
It is like conditional formatting but trough a formula.

Your help is already greatly appreciated.

Ebru

[FlameRetired]

But it should be delimited to columns search:

Does this more closely do that?

Edit By the way loginjmor's formula produces the same results only it's simpler.

Row\Col	A	B	C	D	E	F	G	H
2		Variable 1	Variable 2	Variable 3	Variable 4	Variable 5
3	new requirement	E	B	C	D	E
4
5	produced product 1	A	B	C	D	E	4	Array enter this in G5 and fill down:
6	produced product 2	E	G	H	I	J	1	=SUM(--(INDEX($B$3:$F$3,,N(IF(1,COLUMN($B:$F)-MIN(COLUMN($B:$F))+1)))=INDEX($B5:$F5,,N(IF(1,COLUMN($B:$F)-MIN(COLUMN($B:$F))+1)))))
7	produced product 3	Z	W	R	V	T	0
8	produced product 4	P	L	M	N	F	0
9	produced product 5	S	P	I	E	T	0

[Ebru]

Thanks! I'll give that a go, It looks so complex!
I will be so happy when it will do the job!
Ebru