This is an interesting situation that often comes up. Namely, sometimes one needs to match data in two different columns or different sheets.
So, let’s get started with a simple example, to illustrate how to accomplish this.
Table of Contents
- 1 Introduction
- 2 Using simple Conditional Formatting to find Duplicate or Matching Data
- 3 Using a Formula with the MATCH Function and Conditional Formatting to compare data from two columns
- 4 How to compare two columns in different excel sheets using conditional formatting
- 5 Download Working Files
- 6 Conclusion
- 7 Useful Links
Biochemical researchers working in chemical or pharmaceutical laboratories, developing new drugs, have to order strains of disease causing bacteria, in order to test the effectiveness of the drugs they are developing. One can order strains from microbiological laboratories at some universities or from biotechnological companies. Antibiotic resistance due to bacteria mutating in ways that decrease the effectiveness of drugs is a serious global concern.
Disease-causing bacteria such as Streptococcus, Shigella, Campylobacter, Mycobacterium tuberculosis, Clostridium tetani and Chlamydia particularly are of great interest to the medicinal chemistry and scientific community.
Streptococcus infections can result in relatively mild throat infections but can also cause life-threatening infections of the blood. Shigella infections cause diarrhoea since the intestine is targeted. Campylobacter infections occur as a result of food poisoning and cause diarrhea. Campylobacter is one of the major causes of diarrhoeal diseases. Mycobacterium tuberculosis is responsible for TB infections, one of the oldest known diseases and still causes many deaths globally. Tetanus is caused by Clostridium tetani. Tetanus results in severe stiffening of the jaw muscles. Chlamydia trachomatis is a sexually transmitted infection (STI) caused by bacteria.
In our example, a hypothetical biochemist has contacted two different microbiology labs and noted in two different columns, the names of the disease-causing strains they sell. He intends to, where possible, buy the same strain from the two labs, in order to add extra validity to his experimental design.
The source data is shown below.
Using simple Conditional Formatting to find Duplicate or Matching Data
1) We will first use simple conditional formatting, in order to see the values that are the same (matching) in the two columns.
2) So, first things first, highlight the range A6: B9 as shown below.
3) Go to Home>Styles>Conditional Formatting as shown below.
4) Select Highlight Cells Rules, Duplicate Values.
5) Choose a Format in the Duplicate Values dialog box as shown below.
6) Click Ok, and the duplicate values i.e matching values are highlighted.
Using a Formula with the MATCH Function and Conditional Formatting to compare data from two columns
1) We now are going to use the MATCH Function with conditional formatting, in order to compare the data in column A, with the data in column B, our source data is the same as for the simple conditioning example.
Read More: How to Use Wildcards in Excel?
2) First things first, let’s clear the simple conditional formatting from the cells, by highlighting the range A6: B9 as shown below.
3) Go to Home>Styles>Conditional Formatting>Clear Rules>Clear Rules from Selected Cells.
4) Now we are going to compare the values in Column A with Column B and use the MATCH Function to see where there is a matching value using conditional formatting.
5) This time we highlight only A6: A9 as shown below.
6) Go to Home>Styles>Conditional Formatting>New Rule…. as shown below.
7) In the New Formatting Rule Dialog Box, select the Use a formula to determine which cells to format, and enter the following formula:
=MATCH(A6, $B$6: $B$9, 0)
8) Select the Format……. Button.
9) In the Format Cells dialog box, using the Fill Tab, select a Green Fill as shown below.
10) Select Ok and then OK again and in this case, the values in column A that have matching values in column B, are highlighted only, as shown below.
How to compare two columns in different excel sheets using conditional formatting
In this case, we have the data from the first lab stored on the first sheet called LabOne, and the data from the second lab on the sheet called LabTwo. The source data is shown below.
1) On the sheet called LabOne, highlight the range A6: A9 as shown below.
2) Go to Home>Styles>Conditional Formatting>New Rule and Use a formula to determine which cells to format and enter the following formula as shown below.
Choose a Blue Fill in the Format option.
3) Click Ok.
In this case, he COUNTIF Formula is evaluating the range on the second sheet, and if there is a single match as designated by the =1 in the formula, applying the formatting to the match in the first sheet.
And there you have it.
Download Working Files
Simple Conditional formatting and using conditional formatting in conjunction with a formula provides an effective way to locate matching data either in columns on the same sheet or on different sheets. Since once is using conditional formatting, the visual emphasis is placed on matching data.
Please feel free to tell comment and tell us if you use conditional formatting to locate matching data.