在Google电子表格中标记重复的条目 [英] Mark Duplicate Entries in Google Spreadsheet
问题描述
我有一个电子表格,列F中的条目可能会在F中重复。我正在寻找一些类似于这种伪代码的东西:
Ax不为空
如果Gx中的值为空
如果单元格Ex与其他单元格相同Ey
或
如果单元格Fx与其他单元格相同Fy
THEN
将Gy标记为复制
斜体行y
有关使用Google内置应用程序脚本进行工作的任何建议?
免责声明:我对JS不熟悉,但我在尝试。 div>
你不需要JS。您可以使用内置的电子表格公式来做到这一点。
这听起来像你想要一个类似的答案,我给了,不同之处在于你正在检查两列而不是一列,而不是一列。 。
您需要:
= if(AND(COUNTIF $ A $ 1:$ A2,A2)= 1,COUNTIF($ B $ 1:$ B2,B2)= 1),,是)
要注意的关键是使用 AND
forumla。
这会在后续行中填满并看起来像这样:
= if(AND(COUNTIF($ A $ 1:$ A3,A3)= 1,COUNTIF($ B $ 1:$ B3,B3)= 1),,是)
= if (COUNTIF($ A $ 1:$ A4,A4)= 1,COUNTIF($ B $ 1:$ B4,B4)= 1),,是)
...
这些是以您的电子表格数据为例的结果。假设公式被插入 Duplicate?
列(C2)并填充:
ABC
1联系人姓名重复?
2 email@example.com John
3 repeat.email@example.com重复名称
4 repeat.email@example.com简是
5 email3@example.com重复名称是
I have a spreadsheet with entries in column F that could be duplicates later on in F. I'm looking to make something that does something like this pseudocode:
While Ax is not empty
If value in Gx is empty
If cell Ex is identical to other cell Ey
OR
If cell Fx is identical to other cell Fy
THEN
Mark Gy as duplicate
italics row y
Any recommendations on making this work using Google's built in app scripting?
Disclaimer: I'm not familiar with JS but I'm trying.
You don't need JS for this. You can do it with the built-in spreadsheet formulas.
It sounds like you want a similar answer that I gave to this question, with the difference being that you are checking two columns instead of just one.
You want this:
=if(AND(COUNTIF($A$1:$A2,A2)=1, COUNTIF($B$1:$B2,B2)=1), "", "Yes")
The key thing to notice is the use of the AND
forumla.
This will fill down and look like this in subsequent rows:
=if(AND(COUNTIF($A$1:$A3,A3)=1, COUNTIF($B$1:$B3,B3)=1), "", "Yes")
=if(AND(COUNTIF($A$1:$A4,A4)=1, COUNTIF($B$1:$B4,B4)=1), "", "Yes")
...
And these are the results using your spreadsheet data as an example. This is assuming the formula was inserted into the Duplicate?
column (C2) and filled down:
A B C
1 Contact Name Duplicate?
2 email@example.com John
3 repeat.email@example.com Repeated Name
4 repeat.email@example.com Jane Yes
5 email3@example.com Repeated Name Yes
这篇关于在Google电子表格中标记重复的条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!