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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Is there a way to compare 4 rows of numbers up to 6 characters and result back number of matches in each row?
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.
Upvote
0
shg
MrExcel MVP
- Joined
- May 7, 2008
- Messages
- 21,845
- Office Version
- 2010
- Platform
- Windows
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
[tr][td="bgcolor:#C0C0C0"]
[tr][td="bgcolor:#C0C0C0"]
[tr][td="bgcolor:#C0C0C0"]
[/table]
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]
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,530
- Office Version
- 365
- Platform
- Windows
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?
Last edited:
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,530
- Office Version
- 365
- Platform
- Windows
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...
Code:
[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.
Upvote
0
