Clever Geek Handbook

How can I combine columns from different sheets without duplicates?

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

Example:

Sheet 1

ID
1
2
3

and Sheet 2

ID
1
6
7
3

become Sheet 3

1
2
3
6
7
0


source to share


1 answer


This can be done by extending the usual formula to enumerate unique values ​​that would be

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

for the first list

and

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

for the second list

In pseudocode

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)

So the combined formula would be



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

starting from Sheet3! A2.

These are all array formulas.

This version only works for numbers.

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 Does not sort

x Does not skip spaces

✓ Preserves order if lists are already in ascending order.

+1


source






More articles:


All Articles