Skip to content Skip to sidebar Skip to footer
Página inicial / How to Get the Difference of a Set and a Family of Sets

How to Get the Difference of a Set and a Family of Sets

Postar um comentário

Sentry Video – Compare ii Columns in Excel for matches and differences

The one query that I get a lot is – 'how to compare two columns in Excel?'.

This can be washed in many different ways, and the method to utilise will depend on the data structure and what the user wants from it.

For instance, you may desire to compare two columns and find or highlight all the matching data points (that are in both the columns), or only the differences (where a data point is in ane column and non in the other), etc.

Since I get asked nigh this so much, I decided to write this massive tutorial with an intent to embrace most (if not all) possible scenarios.

If y'all find this useful, do pass information technology on to other Excel users.

Note that the techniques to compare columns shown in this tutorial are non the merely ones.

Based on your dataset, you may need to change or adjust the method. Even so, the basic principles would remain the same.

If yous think there is something that can be added to this tutorial, permit me know in the comments department

Compare Two Columns For Exact Row Friction match

This one is the simplest class of comparison. In this case, you need to practise a row past row comparing and identify which rows have the same data and which ones does not.

Example: Compare Cells in the Same Row

Below is a data set where I need to check whether the proper noun in cavalcade A is the same in column B or not.

Compare Columns - row by row - dataset

If there is a match, I need the result as "TRUE", and if doesn't lucifer, then I need the consequence every bit "Fake".

The below formula would exercise this:

=A2=B2

Compare Lists in Excel - matches are shown as TRUE

Example: Compare Cells in the Same Row (using IF formula)

If you want to get a more descriptive outcome, you can use a simple IF formula to return "Lucifer" when the names are the same and "Mismatch" when the names are dissimilar.

=IF(A2=B2,"Match","Mismatch")

If formula to compare columns in Excel

Notation: In instance you desire to make the comparison instance sensitive, utilize the post-obit IF formula:

=IF(EXACT(A2,B2),"Lucifer","Mismatch")

With the higher up formula, 'IBM' and 'ibm' would be considered two different names and the above formula would render 'Mismatch'.

Example: Highlight Rows with Matching Data

If you want to highlight the rows that have matching data (instead of getting the outcome in a divide cavalcade), you can do that by using Conditional Formatting.

Here are the steps to do this:

  1. Select the unabridged dataset.
  2. Click the 'Home' tab.Click the Home Tab in the Excel ribbon
  3. In the Styles group, click on the 'Conditional Formatting' choice.Click on Conditional Formatting
  4. From the drop-downwardly, click on 'New Dominion'.Click on the New Rule option
  5. In the 'New Formatting Rule' dialog box, click on the 'Employ a formula to determine which cells to format'.Click on Use Formula option
  6. In the formula field, enter the formula: =$A1=$B1Formula to compare columns in Conditional Formatting
  7. Click the Format push button and specify the format you want to apply to the matching cells.Set Formatting in conditional formatting
  8. Click OK.

This will highlight all the cells where the names are the same in each row.

Compare two columns and highlight matching rows

Compare Two Columns and Highlight Matches

If yous desire to compare two columns and highlight matching data, you tin can use the duplicate functionality in provisional formatting.

Note that this is different than what we accept seen when comparison each row. In this case, we volition non exist doing a row past row comparison.

Example: Compare Two Columns and Highlight Matching Data

Frequently, you'll get datasets where there are matches, but these may not be in the aforementioned row.

Something equally shown below:

Compare two columns and highlight macthes - dataset

Note that the list in cavalcade A is bigger than the 1 in B. Besides some names are in that location in both the lists, merely not in the same row (such as IBM, Adobe, Walmart).

If you want to highlight all the matching company names, you can do that using conditional formatting.

Here are the steps to practise this:

  1. Select the unabridged data fix.
  2. Click the Home tab.
  3. In the Styles group, click on the 'Conditional Formatting' option.Click on Conditional Formatting
  4. Hover the cursor on the Highlight Cell Rules option.
  5. Click on Duplicate Values.Select Duplicate Values in Conditional Formatting
  6. In the Duplicate Values dialog box, make sure 'Duplicate' is selected.Duplicate in Conditional Formatting
  7. Specify the formatting.Specify the formatting in conditional formatting
  8. Click OK.

The above steps would requite you the result every bit shown beneath.

Highlighted matching data when comparing lists in Excel

Note: Conditional Formatting duplicate rule is non case sensitive. And then 'Apple' and 'apple' are considered the same and would exist highlighted every bit duplicates.

Example: Compare Ii Columns and Highlight Mismatched Data

In example you want to highlight the names which are nowadays in i list and not the other, yous can use the conditional formatting for this likewise.

  1. Select the entire information prepare.
  2. Click the Home tab.
  3. In the Styles group, click on the 'Conditional Formatting' option.Click on Conditional Formatting
  4. Hover the cursor on the Highlight Cell Rules selection.
  5. Click on Indistinguishable Values.Select Duplicate Values in Conditional Formatting
  6. In the Duplicate Values dialog box, make sure 'Unique' is selected.Select Unique to highlight differences
  7. Specify the formatting.Specify the formatting to highlight differences in two columns
  8. Click OK.

This will give you the result as shown beneath. Information technology highlights all the cells that accept a proper name that is not present on the other list.

Compare Two columns and highlight differences

Compare Two Columns and Observe Missing Data Points

If yous want to identify whether a data point from one listing is nowadays in the other list, you demand to use the lookup formulas.

Suppose you have a dataset as shown below and you want to identify companies that are nowadays in column A but non in Cavalcade B,

Compare two columns and highlight macthes - dataset

To do this, I can employ the following VLOOKUP formula.

=ISERROR(VLOOKUP(A2,$B$two:$B$x,1,0))

This formula uses the VLOOKUP role to bank check whether a company name in A is nowadays in column B or non. If information technology is present, it will return that name from column B, else information technology will render a #North/A error.

These names which return the #N/A mistake are the ones that are missing in Column B.

ISERROR office would render TRUE if there is the VLOOKUP event is an error and Simulated if information technology isn't an error.

compare lists and find missing data

If you want to get a list of all the names where there is no friction match, you can filter the result column to get all cells with TRUE.

You lot tin besides use the MATCH function to do the same;

=Non(ISNUMBER(Lucifer(A2,$B$ii:$B$10,0)))

Annotation: Personally, I adopt using the Match function (or the combination of Index/MATCH) instead of VLOOKUP. I discover it more than flexible and powerful. You can read the deviation between Vlookup and Index/Match here.

Compare Ii Columns and Pull the Matching Information

If you accept two datasets and you want to compare items in ane list to the other and fetch the matching data point, you need to use the lookup formulas.

Example: Pull the Matching Data (Exact)

For example, in the below list, I want to fetch the marketplace valuation value for column 2. To do this, I need to wait up that value in column i and so fetch the corresponding marketplace valuation value.

Compare two lists in Excel and fetch matching data

Below is the formula that will do this:

=VLOOKUP(D2,$A$two:$B$14,2,0)

or

=INDEX($A$2:$B$14,MATCH(D2,$A$2:$A$14,0),2)

Lookup and Pull matching data - market valuation Excel

Example: Pull the Matching Information (Partial)

In example you lot get a dataset where there is a minor deviation in the names in the two columns, using the above-shown lookup formulas is not going to work.

These lookup formulas need an exact match to requite the right issue. At that place is an judge match selection in VLOOKUP or MATCH function, only that can't be used here.

Suppose you have the data ready every bit shown below. Note that at that place are names that are not complete in Column 2 (such equally JPMorgan instead of JPMorgan Chase and Exxon instead of ExxonMobil).

Pull matching Data - partial match

In such a case, you can employ a partial lookup by using wildcard characters.

The following formula will requite is the right issue in this case:

=VLOOKUP("*"&D2&"*",$A$2:$B$14,2,0)

or

=Alphabetize($A$2:$B$14,MATCH("*"&D2&"*",$A$2:$A$14,0),two)

Partial comparison in columns with wildcard characters

In the above example, the asterisk (*) is a wildcard character that tin represent any number of characters. When the lookup value is flanked with information technology on both sides, any value in Cavalcade i which contains the lookup value in Column 2 would be considered as a match.

For example, *Exxon* would be a match for ExxonMobil (as * can represent any number of characters).

You May Also Similar the Following Excel Tips & Tutorials:

  • How to Compare Two Excel Sheets (for differences)
  • How to Highlight Blank Cells in Excel.
  • Highlight EVERY Other ROW in Excel.
  • Excel Avant-garde Filter: A Complete Guide with Examples.
  • Highlight Rows Based on a Cell Value in Excel.

stewartsompter.blogspot.com

Source: https://trumpexcel.com/compare-two-columns/

Postar um comentário for "How to Get the Difference of a Set and a Family of Sets"