Matching concatenated values across columns VBA

[Oregon92]

Hi there!

I am trying to write VBA code to execute the following (visual screenshot attached)

If the concatenated store and zip code value (ex: Walmart98101) is repeated in any of the columns (ex: columns C, E and F), I need to ensure the value of row 1 is the same in each instance of the concatenation match. If the row 1 value does not match, I need a message box to display, alerting me of a mismatch (such would be the case with column E, as 2009 <> 2003). The message box is simple, but I have been going back and forth on how the code should look to align to the logic above, but can’t figure it out. It seems like I am making it more difficult than it needs to be. I thought I had something, but then realized I was only comparing to the immediate right column, instead of comparing across all columns. So in my example screenshot, although there are 3 instances of Walmart98101, my code wasn’t able to recognize any of them.

I appreciate any help, guidance or direction in advance!

 

[Peter_SSs]

See if this does what you want

Sub CheckStoreZip()
  Dim d As Object
  Dim a As Variant
  Dim j As Long
  Dim s As String, sMsg As String
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("C1", Range("C1").End(xlToRight)).Resize(4).Value
  For j = 1 To UBound(a, 2)
    s = a(2, j) & a(4, j) & ": "
    If InStr(1, d(s) & ",", "," & a(1, j) & ",") = 0 Then d(s) = d(s) & "," & a(1, j)
  Next j
  For j = 1 To d.Count
    If InStr(2, d.Items()(j - 1), ",") > 0 Then sMsg = sMsg & vbLf & d.Keys()(j - 1) & Mid(d.Items()(j - 1), 2)
  Next
  If Len(sMsg) > 0 Then MsgBox "Check these:" & sMsg
End Sub

For this sample data ..

Oregon92.xlsm
	C	D	E	F	G	H	I	J	K
1	2003	2010	2009	2003	2008	2008	2001	2002	2003
2	Walmart	Fred Meyer	Walmart	Walmart	Walmart	Walmart	Other	Other	Other
3
4	98101	90001	98101	98101	99999	99999	88888	88888	88888
Sheet1

.. this is the code result ..

 

[Oregon92]

See if this does what you want

Sub CheckStoreZip()
  Dim d As Object
  Dim a As Variant
  Dim j As Long
  Dim s As String, sMsg As String
 
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("C1", Range("C1").End(xlToRight)).Resize(4).Value
  For j = 1 To UBound(a, 2)
    s = a(2, j) & a(4, j) & ": "
    If InStr(1, d(s) & ",", "," & a(1, j) & ",") = 0 Then d(s) = d(s) & "," & a(1, j)
  Next j
  For j = 1 To d.Count
    If InStr(2, d.Items()(j - 1), ",") > 0 Then sMsg = sMsg & vbLf & d.Keys()(j - 1) & Mid(d.Items()(j - 1), 2)
  Next
  If Len(sMsg) > 0 Then MsgBox "Check these:" & sMsg
End Sub

For this sample data ..

Oregon92.xlsm
	C	D	E	F	G	H	I	J	K
1	2003	2010	2009	2003	2008	2008	2001	2002	2003
2	Walmart	Fred Meyer	Walmart	Walmart	Walmart	Walmart	Other	Other	Other
3
4	98101	90001	98101	98101	99999	99999	88888	88888	88888
Sheet1

.. this is the code result ..

View attachment 62809

Hi Peter!

First off, thank you so much for your help! Let me add a little more context, clarification below.

I have to validate a handful of things, and this is one of the earlier stages of the validation. Before I continue to validate additional steps, I need to first ensure that the criteria checks out, otherwise, I will display a message box and alert the user that the code is going to end because the validation failed - aka, the row 1 values didn't match up correctly against the row 2 and row 4 concatenation value.

Sometimes there aren't any direct matches between the concatenation of rows 2 and 4 across any of the columns. That's totally fine. In the case that there are 2+ column matches, I need to validate that the row 1 value is also the same for all of those matches. If the row 1 value is different for any of them, that's when I would display the message and end the code.

My original thought process was to create a loop, that takes the row 2/row 4 concatenated value and searches all the columns to see if a match exists. If it doesn't find a match, the loop continues (column d, row 2/row 4 concatenated value across columns C, E and F). If it does find a match, it compares the row 1 value across the columns that match, to ensure that the row 1 value matches, and if not, displays the message and ends the code. I'm just not sure how that would work, without running into an issue.

I think your code will work! I ran it and changed the values around a bit, to create specific scenarios that would and wouldn't cause a pop-up message. It seemed to display a message when I was hoping it would, and for the times where everything checked out, a message wasn't displayed (which is also what I hoped). I've never used the createobject dictionary and the rest of the code was also way over my head so I'm not 100% sure if it's working as intended or not. With the additional context I provided, is there any reason you would change anything, or do you feel comfortable with the results as is?

Thank you again!

 

[Oregon92]

Hi Peter!

First off, thank you so much for your help! Let me add a little more context, clarification below.

I have to validate a handful of things, and this is one of the earlier stages of the validation. Before I continue to validate additional steps, I need to first ensure that the criteria checks out, otherwise, I will display a message box and alert the user that the code is going to end because the validation failed - aka, the row 1 values didn't match up correctly against the row 2 and row 4 concatenation value.

Sometimes there aren't any direct matches between the concatenation of rows 2 and 4 across any of the columns. That's totally fine. In the case that there are 2+ column matches, I need to validate that the row 1 value is also the same for all of those matches. If the row 1 value is different for any of them, that's when I would display the message and end the code.

My original thought process was to create a loop, that takes the row 2/row 4 concatenated value and searches all the columns to see if a match exists. If it doesn't find a match, the loop continues (column d, row 2/row 4 concatenated value across columns C, E and F). If it does find a match, it compares the row 1 value across the columns that match, to ensure that the row 1 value matches, and if not, displays the message and ends the code. I'm just not sure how that would work, without running into an issue.

I think your code will work! I ran it and changed the values around a bit, to create specific scenarios that would and wouldn't cause a pop-up message. It seemed to display a message when I was hoping it would, and for the times where everything checked out, a message wasn't displayed (which is also what I hoped). I've never used the createobject dictionary and the rest of the code was also way over my head so I'm not 100% sure if it's working as intended or not. With the additional context I provided, is there any reason you would change anything, or do you feel comfortable with the results as is?

Thank you again!

You know what, I think this is perfect. Seems to be doing what it needs to, I just don't necessarily understand how you did it, haha. AMAZING WORK!

 

[Peter_SSs]

Glad it worked for you. Thanks for the follow-up.