如何合并不同工作表中没有重复的列？

Question

如何将来自两个不同工作表的两列数据合并为第三个工作表上的一列而不存在重复项？

例如：

表1

ID
1
2
3

和第2页

ID
1
6
7
3

变成第3页

1
2
3
6
7

Answer 1

可以通过扩展通常列出唯一值的公式来做到这一点，该公式如下

=INDEX(Sheet1!$A$2:$A$4, MATCH(0, COUNTIF($a$1:a1, Sheet1!$A$2:$A$4), 0))

第一份清单

和

=INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($a$1:a1, Sheet2!$A$2:$A$5), 0))

第二份名单

伪码

If at end of first list
    If at end of second list
        Show nothing
    Else
        Show next item from second list 
Else
    If at end of second list
        Show next item from first list
    ELse
        Show smaller of (next item from first list, next item from second list)

所以合并的公式是

=IF(ISERROR(INDEX(Sheet1!$A$2:$A$4, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$4), 0))),
   IF(ISERROR(INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))),
   "",
   INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))),
   IF(ISERROR(INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))),
    INDEX(Sheet1!$A$2:$A$4, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$4), 0)),
    MIN(INDEX(Sheet1!$A$2:$A$4, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$4), 0)),
     INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0)))
 ))

从Sheet3!A2开始。

所有这些都是数组公式。

此版本仅适用于数字。

一个适用于数字和文本的版本：-

=IF(ISERROR(INDEX(Sheet1!$A$2:$A$4,MATCH(0,COUNTIF($A$1:A1,Sheet1!  $A$2:$A$4),0))),
  IF(ISERROR(INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!  $A$2:$A$5),0))),
  "",
  INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0))),
  IF(ISERROR(INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0))),
  INDEX(Sheet1!$A$2:$A$4,MATCH(0,COUNTIF($A$1:A1,Sheet1!$A$2:$A$4),0)),
   IF( INDEX(Sheet1!$A$2:$A$4,MATCH(0,COUNTIF($A$1:A1,Sheet1!$A$2:$A$4),0))
   <INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0)),
    INDEX(Sheet1!$A$2:$A$4,MATCH(0,COUNTIF($A$1:A1,Sheet1!$A$2:$A$4),0)),
    INDEX  (Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0)))
 ))

X不排序

X不会跳过空格

如果列表已经按升序排列，✓确实会保留顺序。