How can we perform common set operations union intersection minus in MS Excel

0 votes

For example, I have an xls where :

  • column A has list of items with property A
  • column B has list of items with property B

I need the following :

  • column C which is A union B (unique items of both A & B)
  • column D which is A intersection B (common items of A & B)
  • column E which is A minus B (items in A but not in B)
  • column F which is B minus A (items in B but not in A)

Set operations on a list of elements seem to be easy with SQL or Python. But how to do it in xls?

Note : It should be an automation with minimal copy-pastes and clicks. For example, I dont want to copy-paste A below B, then "eliminate duplicates" to get A union B.

Mar 30, 2022 in Database by Edureka
• 13,690 points
1,817 views

1 answer to this question.

0 votes

The MATCH function can be used as follows:

MATCH(value, array, match type) returns the value's relative position in the array. Match type is greater than, less than, or equal to.


If #VALUE is returned by an Exact MATCH, it signifies the value isn't in the array.
If it returns a positive number, it indicates that the value is an array.
IFERROR was used to distinguish between #VALUE (set to 0) and positive value (set to 1 using IF condition)
=IFERROR(IF(MATCH(A2,$B$2:$B$16,0)>0,1,0),0) is a function.

image

answered Mar 31, 2022 by gaurav
• 23,260 points

Related Questions In Database

+1 vote
1 answer

how to change format of date from mm/dd/yyyy to dd-mmm-yyyy in MS Excel

First, pick the cells that contain dates, ...READ MORE

answered Apr 11, 2022 in Database by gaurav
• 23,260 points
158,363 views
0 votes
0 answers

How can I set a custom date time format in Oracle SQL Developer?

Date values are shown in Oracle SQL ...READ MORE

Aug 12, 2022 in Database by Kithuzzz
• 38,020 points
758 views
0 votes
1 answer

What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL ...READ MORE

answered Oct 29, 2018 in Database by Sahiti
• 6,370 points
1,163 views
0 votes
1 answer

What are set operations used in database?

There are mainly three set operations: UNION, ...READ MORE

answered Nov 19, 2018 in Database by Sahiti
• 6,370 points
3,137 views
0 votes
1 answer

How can I check my all existing table in MySQL database?

Hi Pratik, I understand your problem regarding this ...READ MORE

answered Jul 1, 2019 in Database by sampriti
• 1,120 points
1,373 views
+1 vote
1 answer

How can I reset my auto_increment column in MYSQL?

Hi Pratim, I understand your issue. I think you ...READ MORE

answered Jul 2, 2019 in Database by sampriti
• 1,120 points
928 views
0 votes
1 answer

Can we store images in database

Beware that serving images from DB is ...READ MORE

answered Oct 4, 2019 in Database by Daric
• 500 points
844 views
0 votes
1 answer

Why do we need Transaction in Hibernate for read-only operations?

Hello @kartik, It doesn't matter whether you only ...READ MORE

answered May 4, 2020 in Database by Niroj
• 82,880 points
2,840 views
0 votes
1 answer

How can we achieve excel ceiling function with significance value in SQL Server

If the underlying data types and rounding ...READ MORE

answered Apr 4, 2022 in Database by gaurav
• 23,260 points
891 views
0 votes
1 answer

How can I calculate deciles with a range of 12,000 cells in excel?

1. Enter the following formula in cell ...READ MORE

answered Mar 25, 2022 in Database by gaurav
• 23,260 points
8,674 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP