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 Name1Value1A100B150C150D201E51F30G101 Data 2 Name 2Value 2rabbit201tiger300mouse151mouse131 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.) Name1Value1Name2Value2A100mouse151B150tiger300C150tiger300D201rabbit201E51mouse151F30mouse131G101mouse131

match based on numbers

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

Common Use Cases

Fuzzy Match

Join

Accepted answers

All comments

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

Matchbasedonnumbers.yxmd

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+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.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235