I've got two ranges in excel.
I need to do the following:
1). Count how many equal values I have, apart from zero. In my example it should be 2 (1 and 8). I found this formula: SUMPRODUCT(--(A2:E2=A3:E3)), but it will match only B1, B2, ignoring that number 8 appeared two times as well.
2). Separately, I need to have these repeated values in a single cell, separated with comma, just like "1,8".
Try this SUM over COUNTIFS array¹ formula,
=SUM(COUNTIFS(A2:E2, "<>"&0, A2:E2, A3:E3))
¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.
For the latter half of your question, I'll offer this rudimentary UDF which strings together the matched values. As a programming enthusiast you should get great pleasure in modifying the code to include a frequency count.
Function stringMatches(rng1 As Range, rng2 As Range, _
Optional sDELIM As String = ", ", _
Optional bNOZERO As Boolean = True)
Dim sTMP As String, rng As Range
stringMatches = vbNullString
For Each rng In rng1
If (CBool(Application.CountIf(rng2, rng.Value)) And Not bNOZERO) Or _
(CBool(Application.CountIfs(rng2, "<>" & 0, rng2, rng.Value)) And bNOZERO) Then
sTMP = sTMP & rng.Value & sDELIM
End If
Next rng
If CBool(Len(sTMP)) Then _
stringMatches = Left(sTMP, Len(sTMP) - Len(sDELIM))
End Function
simply use this (non array) formula:
=SUMPRODUCT((COUNTIFS(A2:E2,"<>0",A2:E2,A3:E3)>0)*1)
but for the second part, i don't think there is a dynamically way without VBA :/
as UDF i suggest something like this: (for the second part only)
Public Function getDoubles(rng1 As Range, rng2 As Range) As String
Dim cell As Variant, str As String
For Each cell In rng1.Value
If cell <> 0 And Not IsError(Application.Match(cell, rng2, 0)) Then str = str & cell & ", "
Next
getDoubles = Left(str, Len(str) - 2)
End Function
But keep in mind: having a value multiple times in one range, the formula/UDF will pretty much likely mess up
To do it in a clean way (skipping all doubles) you can use this:
Public Function getDoubles(rng1 As Range, rng2 As Range, Optional getList As Boolean, Optional compType As VbCompareMethod = vbTextCompare) As Variant
If rng1.Count = 1 Then
getDoubles = Not IsError(Application.Match(rng1.Value, rng2, 0))
Exit Function
ElseIf rng2.Count = 1 Then
getDoubles = Not IsError(Application.Match(rng2.Value, rng1, 0))
Exit Function
End If
Dim tempCol As New Collection
Dim colItem As Variant
Dim isInCol As Boolean
Dim rngItem As Variant
For each rngItem in rng1.Value
isInCol = False
If Len(rngItem) > 0 And rngItem <> 0 Then 'remove the "And getOut <> 0" to do it also for 0's
For Each colItem In tempCol
isInCol = (StrComp(colItem, rngItem, compType) = 0)
If isInCol Then Exit For
Next
If Not isInCol Then tempCol.Add rngItem
End If
Next
Dim getOut As Variant
If getList Then
getOut = ""
Else
getOut = 0
End If
For Each colItem In tempCol
For Each rngItem In rng2
If StrComp(colItem, rngItem, compType) = 0 Then
If getList Then
getOut = getOut & colItem & ", "
Else
getOut = getOut + 1
End If
Exit For
End If
Next
Next
If getList Then
getDoubles = Left(getOut, Len(getOut) - 2)
Else
getDoubles = getOut
End If
End Function
If one (or both) range is only one item, it will return true if it is inside the other range, else it will be false.
Having 2 ranges of at least 2 cells it will output as folows without doubles:
=getDoubles(range1,range2) = the count of matches
=getDoubles(range1,range2,1) = the "," separated list of matches
=getDoubles(range1,range2,0,0) = like the first but case sensitive
=getDoubles(range1,range2,1,0) = like the second but case sensitive
Try this simple UDF():
Public Function compare(r1 As Range, r2 As Range) As Long
Dim r As Range, v As Variant, v2 As Variant
Dim rr As Range
For Each r In r1
v = r.Value
If v <> 0 And v <> "" Then
For Each rr In r2
v2 = rr.Value
If v = v2 Then compare = compare + 1
Next rr
End If
Next r
End Function
User Defined Functions (UDFs) are very easy to install and use:
If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx
To remove the UDF:
To use the UDF from Excel:
=myfunction(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
NOTE:
If there are 4 qwerty in the second range, you will get a count for each one. (but a slight mod can avoid this)
This routine will return the CSV:
Public Function compare2(r1 As Range, r2 As Range) As String
Dim r As Range, v As Variant, v2 As Variant
Dim rr As Range
For Each r In r1
v = r.Value
If v <> 0 And v <> "" Then
For Each rr In r2
v2 = rr.Value
If v = v2 Then compare2 = compare2 & "," & CStr(v)
Next rr
End If
Next r
If compare2 <> "" Then compare2 = Mid(compare2, 2)
End Function
