I am not sure if you need help with the formula per se or with writing an equivalent VBA function that performs the same functionality.
(我不确定您是否需要有关公式本身或编写等效的VBA函数来执行相同功能的帮助。)
Therefore, I am sharing both options: (因此，我要分享这两种选择：)
Using Excel's COUNTIF
(使用Excel的COUNTIF)
Let us assume that your data starts at cell A1 and has a header row.
(让我们假设您的数据始于单元格A1并具有标题行。)
Then, you would have to ... (然后，您将不得不...)
- ... place a formula somewhat similar to the one you already have,
=IF(COUNTIF(B$2:B2, B2) > 1, "x", "") in cell D2 (...在单元格D2中放置一个与您已有的公式类似的公式=IF(COUNTIF(B$2:B2, B2) > 1, "x", ""))
- ... copy the formula in D2 down the cells in column D until you reach the last row of data
(...将D2中的公式复制到D列中的单元格下方，直到到达数据的最后一行)
The reason for using "B$2:B 36 " and "> 1 ", in row 36, for example, (as opposed to "B$2:B 35 " and "> 0 ", the way you had it) is that it allows you to have the formula in all the rows after the header row.
(例如，在第36行中使用“ B $ 2：B 36 ”和“> 1 ”的原因（与您使用它的方式与“ B $ 2：B 35 ”和“> 0 ”相对）允许您在标题行之后的所有行中使用公式。)
Otherwise, you would have to leave D2 with no formula and add the first formula in D3. (否则，您将必须使D2不包含任何公式，并在D3中添加第一个公式。)
The reason for the "$" in "B$2:B2" is that it forces the formula to always search from the top of the table, no matter what row you copy the formula to.
(在“ B $ 2：B2”中使用“ $”的原因是，无论您将公式复制到哪一行，它都会强制公式始终从表的顶部进行搜索。)
The formulas would look something like this:
(公式如下所示：)
Image of sample sheet with COUNTIF formulas
(带有COUNTIF公式的样本表的图像)
Using VBA
(使用VBA)
If you want to use a VBA function instead of Excel's COUNTIF (to hopefully get a faster result), you could create a function such as this:
(如果要使用VBA函数而不是Excel的COUNTIF （以期获得更快的结果），则可以创建如下函数：)
Function ValueRepeats(ByVal valuesRange As Range)
Const headerRow = 1 'If there is no header, you can use 0 instead of 1
'Assume that the value will repeat itself
ValueRepeats = True
Dim values As Variant: values = valuesRange.Value
'If [values] is not an array, it is a single value, meaning that it cannot have a duplicate
If Not IsArray(values) Then
ValueRepeats = False
Exit Function
End If
'Get the 2-dimensional array's bounds
Dim arrLb As Long: arrLb = LBound(values, 1)
Dim arrUb As Long: arrUb = UBound(values, 1)
Dim index2 As Long: index2 = LBound(values, 2) 'In the 2nd dimension, we only
' care about the first column
'Get the value to search for (the last value in the array)
Dim lastValue As Variant: lastValue = values(arrUb, index2)
'Traverse the array and compare the elements against the last value
Dim i As Long
For i = arrLb To arrUb - 1
If ValuesMatch(lastValue, values(i, index2)) Then Exit Function
Next
ValueRepeats = False 'If we are here, no repeat value was found
End Function
Private Function ValuesMatch(ByVal v1 As Variant, ByVal v2 As Variant)
'NOTE: This function treats the string "5" and the number 5 as different values;
' also, string comparisons are case-insensitive,
' and Null, Empty, and "" are considered equivalent; all of this can be changed as needed
Dim typ1 As Integer: typ1 = VarType(v1)
'Make sure the values are of the same type (to avoid confusing numbers and dates),
' unless the values can be converted to an empty string
If typ1 <> VarType(v2) Then
ValuesMatch = (v1 & "") = (v2 & "") 'Null, Empty, and "" will match each other
Exit Function
End If
Select Case typ1
Case vbNull
ValuesMatch = True 'v1=v2 does not work if both values are null
Case vbString
ValuesMatch = StrComp(v1, v2, vbTextCompare) 'Case-insensitive string comparison
Case Else
ValuesMatch = v1 = v2
End Select
End Function
You would then use the formula " =IF(ValueRepeats(B$2:B2), "x", "") " in D2 (instead of the COUNTIF formula) and copy that formula down the rest of column D. The formulas would look something like this:
(然后，您将在D2中使用公式“ =IF(ValueRepeats(B$2:B2), "x", "") ”（而不是COUNTIF公式），然后将该公式复制到D列的其余部分。像这样的东西：)
The only advantage I see in using a VBA function is that it allows you to abort the search after the first duplicate is found.
(我在使用VBA函数时看到的唯一好处是，它使您可以在找到第一个重复项后中止搜索。)
COUNTIF , by contrast, counts all the duplicates, even if you do not need to know that number. (相反， COUNTIF计算所有重复项，即使您不需要知道该数字也是如此。)
Using INDEX/MATCH (a third alternative)
(使用INDEX / MATCH（第三种选择）)
To avoid using VBA and to avoid counting all duplicates with COUNTIF , you can use a formula that utilises Excel's INDEX and MATCH functions.
(为了避免使用VBA并避免使用COUNTIF计数所有重复项，可以使用利用Excel的INDEX和MATCH函数的公式。)
In that case, you would have to leave D2 blank with no formula, and then add a formula such as the following in D3:
(在这种情况下，您必须将D2保留为空白且没有公式，然后在D3中添加如下所示的公式：)
=IF(ISNA(INDEX(B$2:B2, MATCH(B3, B$2:B2, 0),1)), "", "x")
You would then have to copy this formula down the rest of column D. The formulas would look something like this:
(然后，您必须将此公式复制到D列的其余部分。公式看起来像这样：)
Image of sample sheet with INDEX/MATCH formulas
(具有INDEX / MATCH公式的样本表的图像)
To better understand INDEX and MATCH, I would recommend a page such as the following:
(为了更好地理解INDEX和MATCH，我推荐一个如下页面：)
ExcelJet.Net page explaining INDEX/MATCH
(ExcelJet.Net页面解释INDEX / MATCH)
