如何从不同的工作表合并列，而不重复？ [英] How to merge columns from different sheets without duplicates?

问题描述

如何将两列数据从两张不同的表格合并到第三张表中的一列，而不重复？

How can I merge two columns of data from two different sheets into one column on a third sheet without duplicates?

例如：

表1

ID
1
2
3

和
表2

ID
1
6
7
3

成为Sheet 3

1
2
3
6
7

推荐答案

通过扩展通常的公式来列出唯一值，这将是

It's possible to do it by an extension of the usual formula for listing unique values which would be

=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。

starting in Sheet3!A2.

所有这些都是数组公式。

All of these are array formulae.

此版本仅适用于数字。

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

A version that works for numbers and text:-

=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不跳过空白

✓如果列表已经按升序排列，请保留订单。

✓ Does preserve order if lists are already in ascending order.

这篇关于如何从不同的工作表合并列，而不重复？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！