match based on numbers

[JokeFun]

I have two reports that need to be joined/matched with each other. There's no common fields except for the number values, which are also not matching one by one. Like below Example shows. I am seeking your great ideas on this! Thanks in advance.

Data 1

Name1	Value1
A	100
B	150
C	150
D	201
E	51
F	30
G	101

Data 2

Name 2	Value 2
rabbit	201
tiger	300
mouse	151
mouse	131

The expected result. (Let's just assume the numbers are quite nice that there won't be cases like rabbit 300 and tiger 300, or A+B=200 and A+C=200, so that each animal can match to one or multiple letters based on the numbers.)

Name1	Value1	Name2	Value2
A	100	mouse	151
B	150	tiger	300
C	150	tiger	300
D	201	rabbit	201
E	51	mouse	151
F	30	mouse	131
G	101	mouse	131

[Sebastiaandb]

@JokeFun 

This would be the result of a normal join. Could you be more precise of what you want to achieve? There's no way you can join E/50 with mouse/150, then it would be more append fields logic. Just let us know, i'm sure we can help you out :-).

Greetings,

Seb

[JokeFun]

Sorry, one error on value 2 in the third table for tigers. just updated. 

Basically, I need to match the animals with names in table 1 based on the numbers. A simple join wont work, I believe.

[Sebastiaandb]

@JokeFun 

Data 1

Name1	Value1
A	100
B	150
C	150
D	200
E	50

Data 2

Name 2	Value 2
rabbit	200
tiger	300
mouse	150

In your example. A value of  Rabbit 200 (in data 2) can normally only be joined to D 200 (Data 1). A value for tiger wouldn't have a match and mouse would only match with C 150 and B 150 as mouse stands for 150. 

So a normal join would result in:

Name1	Name2	Value
B	Mouse	150
C	Mouse	150
D	Rabbit	200

From trying to follow your logic, you're searching for something that says "if a value of Data 1 is smaller than up to, then it falls in the category of animal x"? For me it's still not clear to what you want to achieve. I can't follow the logic of your example, sorry.

Greeting, 

Seb

[JokeFun]

Sorry, bad numbers. Just updated with more sample data. So actually table 2 is kind of summary of table 1, but not exactly either. Like mouse category, there're two number 141 and 131. The purpose is to find the Name2 for each Name1.

[Nanoq]

@JokeFun Can you explain a bit more on the logic that would match up your values? specifically i have a hard time seeing how tiger is B and C? 

[JokeFun]

@Nanoq It is simply because B+C=300, while tiger is 300. Just based on the numbers.

Basically, I am thinking of a solution like this by building a macro:

take A first, check if the value exist in table 2, if it matches, then get name2. 

if not, then take sum(A+B) and check again if this exist in table 2,

then A+C,

A+D,

....

and then 

A+B+C

A+B+D

...

and

A+C+D

A+C+E

...

and then

B,

B+C

B+D

..

B+C+D

B+C+E

...

B+D+E,

so on so forth

[Nanoq]

@JokeFun by that logic, (A+E) is also 300, and tiger should be A and E too?
how should it be handeled if a single value 1 can be used in multiple value2's?

[JokeFun]

You are right. I just updated the numbers. Suppose there's no multiple matches.