英文:
Excel How do I find duplicate value in each row
问题
以下是翻译好的部分：
"Example table" 可以翻译为 "示例表格"。
"1 row has duplicated value 'c', columns C, D." 可以翻译为 "第1行有重复的值 'c'，在C列和D列。"
"4 row has duplicated value 'd', columns B, C, D." 可以翻译为 "第4行有重复的值 'd'，在B列、C列和D列。"
"5 row has duplicated value 'e', columns A, D." 可以翻译为 "第5行有重复的值 'e'，在A列和D列。"
"I used COUNTIF function, that function need to base column, but duplicated value can occurred any columns." 可以翻译为 "我使用了COUNTIF函数，该函数需要基于列，但重复的值可能出现在任何列。"
"So how do I find duplicate value in each row, using excel function?" 可以翻译为 "那么如何使用Excel函数查找每一行中的重复值？"
英文:
Example table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | a | b | c | c |
| 2 | b | a | d | e |
| 3 | c | d | b | a |
| 4 | b | d | d | d |
| 5 | e | a | d | e |
1 row has duplicated value 'c', columns C, D.
4 row has duplicated value 'd', columns B, C, D.
5 row has duplicated value 'e', columns A, D.
I used COUNTIF function, that function need to base column, but duplicated value can occurred any columns.
So how do I find duplicate value in each row, using excel function?
答案1
得分: 4
Use FILTER() with COUNTIFS() function.
=UNIQUE(FILTER(A1:D1,COUNTIFS(A1:D1,A1:D1)>1,""),1)
If you have chance to have more than 1 duplicate values then wrap it with TEXTJOIN() to concatenate all values into single cell.
=TEXTJOIN(", ",1,UNIQUE(FILTER(A1:D1,COUNTIFS(A1:D1,A1:D1)>1,""),1))
To use the formula as dynamic spill array use BYROW() function.
=BYROW(A1:D5,LAMBDA(x,TEXTJOIN(", ",1,UNIQUE(FILTER(x,COUNTIFS(x,x)>1,""),1))))
英文:
Use FILTER() with COUNTIFS() function.
=UNIQUE(FILTER(A1:D1,COUNTIFS(A1:D1,A1:D1)>1,""),1)
If you have chance to have more than 1 duplicate values then wrap it with TEXTJOIN() to concatenate all values into single cell.
=TEXTJOIN(", ",1,UNIQUE(FILTER(A1:D1,COUNTIFS(A1:D1,A1:D1)>1,""),1))
To use the formula as dynamic spill array use BYROW() function.
=BYROW(A1:D5,LAMBDA(x,TEXTJOIN(", ",1,UNIQUE(FILTER(x,COUNTIFS(x,x)>1,""),1))))
答案2
得分: 0
你可以使用条件格式化>> 高亮显示>> 重复数值来突出显示每一行中的重复单元格。
英文:
You can use
Conditional Formatting>> Highlight>> Duplicate Values
that highlights duplicate cells in each row
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论