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 in Database by Edureka
• 13,600 points
33 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 by gaurav
• 12,420 points

Related Questions In Database

0 votes
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 in Database by gaurav
• 12,420 points
93 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,360 points
589 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,360 points
1,301 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
642 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
311 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
205 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,720 points
1,374 views
0 votes
1 answer

How Can I use "Date" Datatype in sql server?

There's problem in all of them and ...READ MORE

answered Feb 9 in Database by Neha
• 8,560 points
67 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 in Database by gaurav
• 12,420 points
42 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 in Database by gaurav
• 12,420 points
105 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP