Match ranged figures in sheets

[darcus]

Hi

I hope you can help - I can't get my head around using INDEX and MATCH to do the following:

I have two tables set up as follows:

Table 1 - Material Chemical Composition.

For example: C_max and C_min - this is the range of C (Carbon).

Material_BS970-91

Material_BS970-1955

MaterialType

Cond

C_min

C_max

Si_min

Si_max

Mn_min

Mn_max

P_min

P_max

S_min

S_max

Cr_min

Cr_max

Mo_min

Mo_max

Ni_min

Ni_max

Se_min

Se_max

Ti_min

Ti_max

Nb_min

Nb_max

230M07

EN1A

Steel

0

0.15

0

0.05

0.9

1.3

0

0.09

0.25

0.35

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

212A42

EN8DM

Steel

0.4

0.45

0

0.25

1

1.3

0

0.06

0.12

0.2

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

226M44

EN8M

Steel

T

0.4

0.48

0

0.25

1.3

1.7

0

0.06

0.22

0.3

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

080A15

EN32B

Steel

0.13

0.18

0.1

0.4

0.7

0.9

0

0.05

0

0.05

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

080M15

EN32C

Steel

0.12

0.18

0.1

0.4

0.6

1

0

0.05

0

0.05

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

070M20

EN3B

Steel

0.16

0.24

0.1

0.4

0.5

0.9

0

0.05

0

0.05

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

080A30

EN5B

Steel

0.26

0.34

0.1

0.4

0.7

0.9

0

0.05

0

0.05

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

080A42

EN8D

Steel

0.4

0.45

0.1

0.4

0.7

0.9

0

0.05

0

0.05

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

080A47

EN43B

Steel

0.45

0.5

0.1

0.4

0.7

0.9

0

0.05

0

0.05

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

080M50

EN43A

Steel

T

0.45

0.55

0.1

0.4

0.6

1

0

0.05

0

0.05

0

100​

0​

100​

0​

100​

0​

100​

0​

100​

0​

100​

Table 2 - Grade Spec Chemical Limits

Specification

Thread

Class

Style

C_min

C_max

Mn_min

Mn_max

P_min

P_max

S_min

S_max

Pb_min

Pb_max

ISO 898-2

Coarse

04

style 0 0.45d<m<0.8d

0.00​

0.58​

0.25​

0.00​

0.060​

0.00​

0.150​

ISO 898-2

Coarse

05

style 0 0.45d<m<0.8d

0.00​

0.58​

0.30​

0.00​

0.048​

0.00​

0.058​

ISO 898-2

Coarse

5

style 1 m>0.8d

0.00​

0.58​

0.00​

0.060​

0.00​

0.150​

ISO 898-2

Coarse

5

style 1 m>0.8d

0.00​

0.58​

0.00​

0.110​

0.00​

0.340​

0.00​

0.35​

ISO 898-2

Coarse

6

style 1 m>0.8d

0.00​

0.58​

0.00​

0.060​

0.00​

0.150​

ISO 898-2

Coarse

6

style 1 m>0.8d

0.00​

0.58​

0.00​

0.110​

0.00​

0.340​

0.00​

0.35​

ISO 898-2

Coarse

8

style 2 m>0.9d

0.00​

0.58​

0.25​

0.00​

0.060​

0.00​

0.150​

ISO 898-2

Coarse

8

style 1 m>0.8d

0.00​

0.58​

0.30​

0.00​

0.048​

0.00​

0.058​

ISO 898-2

Coarse

9

style 1 m>0.8d

0.00​

0.58​

0.25​

0.00​

0.060​

0.00​

0.150​

ISO 898-2

Coarse

10

style 1 m>0.8d

0.00​

0.58​

0.30​

0.00​

0.048​

0.00​

0.058​

ISO 898-2

Coarse

10

style 2 m>0.9d

0.00​

0.58​

0.30​

0.00​

0.048​

0.00​

0.058​

ISO 898-2

Coarse

12

style 1 m>0.8d

0.00​

0.58​

0.45​

0.00​

0.048​

0.00​

0.058​

ISO 898-2

Coarse

12

style 2 m>0.9d

0.00​

0.58​

0.45​

0.00​

0.048​

0.00​

0.058​

I want to be able to put the 'Class' from table 2 next to those figures in table one where all the Chemical figures match.
So for example against 230M07 in table one, I want it to put 04 in a new column at the end.

Is this possible?

 

[Rijnsent]

Hi Darcus,
I'm trying to compare your tables, but how do you know which row is the matching one? I mean: in table 2 I can't see "230M07", so how would you find the matching row, "all the Chemical figures match"? Could you specify that a bit more?
Thanks,
Koen

 

[darcus]

Hi
Sorry, I've realised I was unclear.

Table 1 contains the material name in column one, columns 5 onward contain chemical composition limits.
In table 2, each class has it's own chemical composition limits.
In essence, i want to compare the two tables and see where there is overlap...
So for example:

Class	Chemical	Class Limit	Materials that Match
04	C	0.00-0.58	230M07 212A42 226M44 080A15 080M15 070M20 080A30 080A42 080A47 080M50
04	Mn	0.25-100.00 (has no maximum, but I will add 100 to the max column so that the formula remains the same for each of the elements in the list)	230M07 212A42 226M44 080A15 080M15 070M20 080A30 080A42 080A47 080M50
04	P	0.00-0.06	212A42 226M44 080A15 080M15 070M20 080A30 080A42 080A47 080M50
04	S	0.00-0.15	080A15 080M15 070M20 080A30 080A42 080A47 080M50

So, after this matching process, we can see that the only ones that match C, Mn, P and S limits are:
080A15
080M15
070M20
080A30
080A42
080A47
080M50

I want to be able to do that for each class. I have a Table 1 materials list that contains nearly 300 materials, so comparing each one by hand is a pain!

Sorry for being unclear, if I can do anything else, please let me know.

D

 

[Rijnsent]

Hi D,
that's probably a tough cookie if you want to use formulas only, probably some VBA is needed (e.g. making a custom function). To clarify, are these cases okay, given e.g. just the C values:
Tbl1: 0,1-0,2 Tbl2: 0,3-0,4 -> no match, no overlap
Tbl1: 0,1-0,2 Tbl2: 0,2-0,3 -> match, one value overlapping
Tbl1: 0,1-0,2 Tbl2: 0,0-0,4 -> match, full overlap
Tbl1: 0,1-0,2 Tbl2: 0,05-0,15 -> match, partial overlap
Do you speak some VBA?
Koen

 

[darcus]

Hi Koen,

Thanks for your response.
There must be full overlap within the ranges.

Yes, I was thinking about using VBA because I couldn't see how to accomplish it with formulae. I know a little VBA. I'll have a bash at writing something and post it here, as soon as I have some time.

Thank you