问题描述
我有两列excel中有一些共享数据和一些独特的数据到每一列。我想要的是再有两列,它们将具有列A中的数据,但不包括B的数据,而列B中的数据却不在A中。我已尝试使用Vlookup,但我不认为我可以得到什么我正在寻找这样做。对于不在列B中的Col A项,请尝试使用数组输入公式(在某些单元格中输入,然后填写,直到返回空白)
= IFERROR(INDEX(ColA, SMALL(IF(ISNA(MATCH(ColA,ColB,0)),ROW(ColA),),ROWS($ 1:1)) - 1),)
和不在列A中的Col B项目
= IFERROR(INDEX(ColB,SMALL(IF(ISNA(MATCH(ColB,ColA,0)),ROW(ColB),),ROWS($ 1:1)) - 1),)
公式假设您的数据从第2行开始(第1行将是一个标签),范围为ColA和ColB也从第2行开始
公式也假定数据中没有空格。如果有空单元格,则使用以下公式:
= IFERROR(INDEX(ColA,SMALL(IF(ISNA ColA,ColB,0))*(ColA),ROW(ColA)),ROWS($ 1:1))-1),)
= IFERROR(INDEX ColB,SMALL(IF(ISNA(MATCH(ColB,ColA,0))*(ColB - ),ROW(ColB)),ROWS($ 1:1)) - 1),)
示例图像:
I have two columns in excel that have some shared data and some unique data to each column. What I want is to have two more columns that will have the data that is in Column A but not B and the data that is in Column B but not in A. I have tried using Vlookups but I don't think I can get what I am looking for by doing this.
For Col A items not in column B, try this array-entered formula (entered in some cell, then fill down until it returns blanks)
=IFERROR(INDEX(ColA,SMALL(IF(ISNA(MATCH(ColA,ColB,0)),ROW(ColA),""),ROWS($1:1))-1),"")
and for Col B items not in Column A:
=IFERROR(INDEX(ColB,SMALL(IF(ISNA(MATCH(ColB,ColA,0)),ROW(ColB),""),ROWS($1:1))-1),"")
The formulas assume that your data starts in row 2 (row 1 would be a label) and the ranges ColA and ColB also start in Row 2
The formulas also assume no blanks in the data. If there are empty cells, then use these formulas:
=IFERROR(INDEX(ColA,SMALL(IF(ISNA(MATCH(ColA,ColB,0))*(ColA<>""),ROW(ColA)),ROWS($1:1))-1),"")
=IFERROR(INDEX(ColB,SMALL(IF(ISNA(MATCH(ColB,ColA,0))*(ColB<>""),ROW(ColB)),ROWS($1:1))-1),"")
Example Image:
这篇关于查找一列中唯一的元素excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!