Hi,
Hopefully someone can help me solve or put me on the right track to solving this problem I've been wracking my brains about.
Basically I have two ranges of related data which have a many-to-many relationship and i'm trying to create a datamap (through the use of a function) which reads through the ranges and identifies where a match has been found.
PersonCode PersonDescription ClothingCode ClothingDesc
P1 Bob C1 Hat
P2 Steve C1 Hat
P1 Bob C2 Coat
P3 Frank C3 Gloves
P2 Steve C3 Gloves
This is basically my many-to-many data range, many people can wear the different clothing and vice versa.
Person P1 P2 P3 P4 etc
Clothing
C1 1 1 0 0
C2 1 0 0 0
C3 0 1 1 0
This is how the data map should look.
I'm thinking that the best way to solve this would be a function called from each cell that takes in P1 and C1 (etc) as parameters and then spits out 1 or 0 as a result.
Function CountMatches(ByVal strPersonCodePosition As String, ByVal strClothingCodePositon As String, ByVal rngPersonCodes As Range, ByVal rngClothingCodes As Range) As Integer
CountMatches = x
End Function
My (main) problem is that I can't seem to find a way to iterate through the ranges based on the cell contents to confirm and then match against the other range.
If anyone could help me I'd be extremely thankful
Thanks in advance.
Cal
Bookmarks