Matching multiple values in 2 columns

[Galileo1980]

Hello, the issue I'm having is that I have 2 columns on different worksheets that contain values that match to each other.

The easy ones are the 1:1 matching, simply take the first value, compare that to the other one and if they match do whatever needs to be done. I loop through the X values, I loop through the Y values and done. The difficult part is when it comes to 1:x matching, which means that 1 value in the first column may be made up by 4 values in the other column. Now in itself that is not a problem, it just requires a heck of a lot of nested loops (and time).

Is there a way to create a function that I can call subsequently and that would do one of the loops? This way I don't have to program all of my loops for 1:5 and could easily upscale to 1:10 if needed. What I was think is that I would pass on the X, Y, Z, AA, AB, AC value as a combined figure (deducting Y,Z from X to make AA match the entire calculation). I just need to know how to loop create a nested looped function.

Function Looping (ValueToMatch)
 For loop = 1 to OneInXMatching
  x = cell.value
  if ValueTomatch = x then perform_match_action
  Looping (ValueToMatch - x)
 Next loop
end function

I hope it's clear ;-)

 

[SkipVought]

Hi,

"1 value in the first column may be made up by 4 values in the other column."

I have no idea what that means.

Can you please post an example?

It is ALWAYS 4 values?

Might it be more or fewer than 4 values?

How do you want the matches identified?

Have you tried any spreadsheet lookup functions?

Please answer ALL questions

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Galileo1980]

No, it's not always 4 values, it's a mixture of 1:1, 1:2, 1:3, 1:4 right up till 1:8 is the most I've seen. Around 80% is 1:1 matching and they should be identified and the color of the cell should change. All of that is no problem to program, but if I want to do 1:5 matching that basically means I have to loop trough the first column and then 5 consecutive loops in the second column.

Have I tried spreadsheet lookup functions, the vlookup doesn't work cause it will only return the first match and be done with that, the find will also return the first as far as I know, but the main point is that I want this done in VBA as I need to do stuff afterwards. I need to give the identified matches a color, insert a comment with the unique match identifier and notify another column so I can easily see which ones aren't matched yet.

Example would be something like:

Column A  -  Column B

12  -  6
15  -  5
13  -  4
21  -  2
300 -  15

In which case running first the 1:1 would return 15 in Column A and 15 in Column B.
Running 1:2 matching would return nothing, as there are no 2 values in Column B that would make up a value in Column A.
Running 1:3 matching would return the 12 in Column A and 6,4,2 in Column B.

 

[SkipVought]

Why is each column not sorted?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Galileo1980]

It is, just not on those columns. I could try and see if I can have it sorted as well on those columns, but what difference would that make? The columns do contain multiple similar values, so vlookups still wouldn't work as it would still pick up the first one.

The main issue I'm having basically that I don't want to hard code my loops, but build a dynamic looping. I could do that using for-next loops but they would have to call themselves somehow because if I want to match 1 value to 3 others the loop would have to be on each of those 3 and downards (it basically compares x with y and z, x with y and AA, x with y and AB, x with z and AA, x with z and AB, etc.)

 

[ettienne]

Running 1:3 matching would return the 12 in Column A and 6,4,2 in Column B.

 

[ettienne]

Oops, click submit post by accident...
So what you are saying is that the SUM of any x values in column B match the value in A?

 

[Galileo1980]

Yes, 1 value in column A matches a number of values in Column B. Just how many is not defined so in the easiest match it would simply match 1 value on Column B. In more difficult situations it would be that 2 values in column B would match 1 value in Column A.

Basically Column A are settlements of daily movements which could consist of many actions. If there's just 1 movement it would simply be that column A matches the movement in Column B, but if there are multiple actions during the day the settlement would be in column A and the multiple items in Column B. I've seen up to 8 actions in 1 day, which would mean 1 settlement of Column A would even match 8 values in column B.

 

[SkipVought]

If the data in EACH column is sorted, MATCH locates the offset row and COUNTIF the number of rows containing the value. What more do you need?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Galileo1980]

They are not unique values, so from my understanding the match function will only pick up the first one and I need to skip the value once it has been matched. Plus I need to know the cell it references, because if I loop through everything I need to give it another color based on the fact that it got matched, I need to know the row number because I'm notifying an adjacent column.

Now I've got that all worked out, it's working fine as it should, it's just that I somehow need to call a function because currently I'm hard coding all my loops and I want to soft code that. If consecutive looping needs to go to 10 that should be a possibility rather then me copying down 10 loops and adjusting variables.

 

[SkipVought]

[tt]
=MATCH(A1,$C$1:$C$5,0)
=MATCH(C1,$A$1:$A$5,0)
A B C D
12 #N/A 6 #N/A
15 5 5 #N/A
13 #N/A 4 #N/A
21 #N/A 2 #N/A
300 #N/A 15 2
[/tt]
is that what you are looking for?
as you

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Galileo1980]

The Match function has 2 problems why I'm not using it:

* It returns only the first hit and I need to skip hits once it has been successfully matched
* It only works for 1 on 1 matching, where as that only represents about 40% of the total matches. The others are 1:x matching where 1 value in column A matches multiple values in Column B.

 

[SkipVought]

Not if you use the COUNTIF function & MATCH function in the OFFSET function.

MATCH gets you to the BEGINNING of the group.
COUNTIF gets you the number of rows in the group.

This is the shorthand for returning the RANGE of the lookup value. (? depends on youw your table is set up)
[tt]
OFFSET(AnchorREF,MATCH(...),?,COUNTIF(...),1)
[/tt]
Then use the INDEX function to pull any value within that range.
[tt]
=INDEX(OFFSET(...),r,c)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}