只将第2个+重复的标记为True,第1个出现的标记为False,使用VBA。

huangapple go评论52阅读模式
英文:

Mark only 2nd + Duplicates as True and 1st occurrence as False occurrences with VBA

问题

查找VBA解决方案,以在新列中标记除第一个值以外的每个重复值为TRUE或FALSE,以便在数据透视表中将它们作为页面筛选器进行过滤。第一次出现的值应标记为FALSE,以便数据仍然显示,但连续的重复值在透视表中隐藏。

.Cells(clclrow, 19) = WorksheetFunction.CountIf(.Range("C1:C" & clclrow), .Cells(clclrow, "C")) + WorksheetFunction.CountIf(.Range("C1:C" & clclrow), .Cells(clclrow, "C")) > 2
或
.Cells(clclrow, 19) = WorksheetFunction.CountIf(.Range("C1:C" & clclrow), .Cells(clclrow, "C")) > 1

其中,clclrow = 2到ws.Cells(Rows.Count, 1).End(xlUp).Row

尝试仍然将所有重复值显示为TRUE。

英文:

Looking for a solution in VBA to mark every duplicate values except the first value in a new column with TRUE or FALSE so that they can be filtered out in a pivot table as a page filter. The first occurrences should be marked as FALSE so the data still shows but sequential values are hidden in the pivot.

                .Cells(clclrow, 19) = WorksheetFunction.CountIf(.Range("C1:C1" & clclrow), .Cells(clclrow, "C")) + WorksheetFunction.CountIf(.Range("C1:C1" & clclrow), .Cells(clclrow, "C")) > 2

or .Cells(clclrow, 19) = WorksheetFunction.CountIf(.Range("C1:C1" & clclrow), .Cells(clclrow, "C"))> 1

where clclrow = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row

The attempt still shows all duplicate values as TRUE.

答案1

得分: 0

我的回答更多地基于逻辑。我会使用一个分隔符,并假设分隔符之间的每个项目在后面有重复项。只需选择并剪切该项目,然后搜索/替换重复项为匹配的文本加上您想要添加的标签,最后将剪切的文本粘贴回其原始位置。继续前进到下一个分隔符并重复。

英文:

My answer is more logic than anything else. I would use a delimiter and assume that every item between the delimiters has duplicates further ahead. Simply select and cut the item, then search/replace the dupes with the matching text plus whatever tag you want to add, and finally paste the cut text back into its original location. Advance beyond the next delimiter and repeat.

答案2

得分: 0

修复方法如下。

temp = (WorksheetFunction.CountIf(.Range("C2:C" & clclrow), .Cells(clclrow, "C")) = 1) + 0
If temp = 0 Then
    .Cells(clclrow, 16).Value = "TRUE"
Else
    .Cells(clclrow, 16).Value = "FALSE"
End If
英文:

Fix it with this method.

temp = (WorksheetFunction.CountIf(.Range("C2:C" & clclrow), .Cells(clclrow, "C")) = 1) + 0
                If temp = 0 Then
                    .Cells(clclrow, 16).Value = "TRUE"
                Else
                    .Cells(clclrow, 16).Value = "FALSE"
                End If

huangapple
  • 本文由 发表于 2023年5月18日 01:51:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/76274920.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定