英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论