Is there a way to compare 4 rows of numbers up to 6 characters and result back number of matches in each row?

[iamlea]

I have 4 columns of numbers (up to 8 characters longer) and I am looking to compare each cell (per individual row) up to 6 characters and result back the number of cells that match. If all 4 cells match I would like it to read "4 matches", if only 3 of the 4 match result should say "3 matches" and so on down to "no matches". Is this possible in a single formula? I have compared 3 columns before but had to break it down into 3 separate formulas.

 

[shg]

For example, ...?

 

[iamlea]

sorry can't figure out how to post just the image -

https://www.dropbox.com/s/rrosvu9p9vh14y4/sample.JPG?dl=0 . I am thinking this might actually be code versus a formula? These are HTS codes and we need to determine if the HTS code provided in each column matches each other up to the first 6 characters. My example does not have a formula, I manually determined each of those examples.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

90278000​

[/td][td]

90278001​

[/td][td]

90278002​

[/td][td]

90278003​

[/td][td="bgcolor:#CCFFCC"]4 matches[/td][td]E2: {=INDEX({"no",2,3,4}, MAX(FREQUENCY(IFERROR(-LEFT(A2:D2, 6), ""), IFERROR(-LEFT(A2:D2, 6), "")))) & " matches"}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

90278000​

[/td][td][/td][td]

90278099​

[/td][td]

90278099​

[/td][td="bgcolor:#CCFFCC"]3 matches[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

90278000​

[/td][td][/td][td]

90279050​

[/td][td]

90279050​

[/td][td="bgcolor:#CCFFCC"]2 matches[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td]

90279050​

[/td][td][/td][td="bgcolor:#CCFFCC"]no matches[/td][td][/td][/tr]
[/table]

 

[Rick Rothstein]

These are HTS codes and we need to determine if the HTS code provided in each column matches each other up to the first 6 characters. My example does not have a formula, I manually determined each of those examples.

What if two cells match one number and the other two cells match a different number (all in the same row)... what should the count be then, 2 or 4?

 

[iamlea]

Rick, great question! for my purposes I would prefer an error, then we could manually review.

 

[iamlea]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[TD="bgcolor: #C0C0C0"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

90278000​

[/TD]
[TD]

90278001​

[/TD]
[TD]

90278002​

[/TD]
[TD]

90278003​

[/TD]
[TD="bgcolor: #CCFFCC"]4 matches[/TD]
[TD]E2: {=INDEX({"no",2,3,4}, MAX(FREQUENCY(IFERROR(-LEFT(A2:D2, 6), ""), IFERROR(-LEFT(A2:D2, 6), "")))) & " matches"}[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

90278000​

[/TD]
[TD][/TD]
[TD]

90278099​

[/TD]
[TD]

90278099​

[/TD]
[TD="bgcolor: #CCFFCC"]3 matches[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

90278000​

[/TD]
[TD][/TD]
[TD]

90279050​

[/TD]
[TD]

90279050​

[/TD]
[TD="bgcolor: #CCFFCC"]2 matches[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

90279050​

[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]no matches[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am running into an error when I copy the code. where is my error?
https://www.dropbox.com/s/e0lnxpe81zvhyz2/Capture.JPG?dl=0

 

[shg]

Paste the formula in the formula bar sans curly braces, press and hold the Ctrl and Shift keys, then press Enter.

Then copy down.

 

[Rick Rothstein]

Rick, great question! for my purposes I would prefer an error, then we could manually review.

My guess shg will be able to modify his formula to account for two separate pairs of numbers in the same row, but until then, here is a UDF (user defined function) that you can use...

[table="width: 500"]
[tr]
	[td]Function Matches(Rng As Range) As String
  Dim Cell As Range
  With CreateObject("Scripting.Dictionary")
    For Each Cell In Rng
      If Len(Cell.Value) Then .Item(Left(Cell.Value, 6)) = .Item(Left(Cell.Value, 6)) + 1
    Next
    Matches = Join(.Items, ",")
    If Matches = "2,2" Then
      Matches = "*** Error ***"
    Else
      Matches = Replace(Evaluate("MAX({" & Matches & "})"), 1, "No") & " matches"
    End If
  End With
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Matches just like it was a built-in Excel function. For example,

=Matches(A2:D2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[iamlea]

Thank you for this! it worked!