excel 2007重复行 [英] excel 2007 duplicate row
问题描述
如何比较表中的记录,以确保这些记录不重复?使用excel 2007我比较后不会删除它们。
重复行应该是彩色的。我有一个表列是从A到P和我有500行。我想把条件放在A,B,E,F,G,I上。
如果你不想排序您的列,您可以尝试使用矩阵公式(http://www.stanford.edu/~wfsharpe/mia/mat/mia_mat4.htm)。
实际上您可以将当前行与上面的每一行进行比较。如下所示:
= MIN(LINE(B1)*(IF(A2 = A1; 1; 0))* (B2 = B1; 1; 0)))*(...)
SHIFT-ENTER将检查所有条件是否为真,否则将返回0。
如果您想要一个实际的例子,请发送一个文件(带匿名数据) / p>
希望有帮助
修改:这里是一个很好的解决方案比较Q列中的数据):
= MIN(LIGNE($ Q $ 5:Q6)* EQUIV(Q6; $ Q $ 5:Q6; 0))
如果要让值出现的第一行
= MIN(LIGNE($ Q $ 5:Q5)* EQUIV(Q6; $ Q $ 5:Q5; 0))
如果您希望#N / A如果该行之前没有重复
仍然使用CTRL-SHIFT-ENTER进行验证
How can I compare records in a table, to make sure these records are not duplicates? Using excel 2007 I don't won’t them to delete after comparison. Duplicates rows should be colored. I have a table columns are from A to P and I have 500 rows. I want to put condition on A, B, E, F, G, I.
If you don't want to sort your column, you can try with a matrix formula (http://www.stanford.edu/~wfsharpe/mia/mat/mia_mat4.htm).
Practically, you can compare your current row to every row above. Somtething like :
=MIN(LINE(B1)*(IF(A2=A1;1;0))*(IF(B2=B1;1;0)))*(...)
validated with CTRL-SHIFT-ENTER will check if all the conditions are true, else, will return 0.
Please send a file (with anonymous data) if you want a practical example.
Hope that helps
Edit : here is the good solution (provided you want to compare data in the Q column) :
=MIN(LIGNE($Q$5:Q6)*EQUIV(Q6;$Q$5:Q6;0))
If you want to have the first line where the value appear
=MIN(LIGNE($Q$5:Q5)*EQUIV(Q6;$Q$5:Q5;0))
If you'd rather have #N/A if there are no duplicate before that line
Still validate with CTRL-SHIFT-ENTER
这篇关于excel 2007重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!