Comparing 4 columns of excel data

Question

Hello,

I am trying com compare 4 columns of variables. I would like to identify variables that appear in all 4 columns of my data. I am wondering if it is possible to do this. I tried using conditional formatting to identify duplicate data, but I realized that this identifies cels that appear more than once, and what I really need is to identify the cells that appear in all 4 columns. Thank you!

Answer 1

Hi,

Please check whether the following solution is helpful:

Step 1) Starting from the 1st cell, select the dataset.

e.g. in the screenshot below, I clicked on cell A2 and selected A2:C8

Step 2) Go to Home tab > click on Conditional Formatting drop-down > click on New Rule > click on Use a formula to determine which cells to format > input the formula: =AND(COUNTIF($A$2:$A$8,A2)>=1,COUNTIF($B$2:$B$8,A2)>=1,COUNTIF($C$2:$C$8,A2)>=1)

Click on Format > apply formatting > click on OK > again, click on OK.

Result - In My dataset, all apples & mangoes are highlighted.

Illustration 2 - I modified the original dataset > I interchanged values of cells A4 & B8 and I interchanged values of cells A6 & C2.

Result - Now, because only mango appears in all 3 columns, all mangoes are highlighted.

Note: in the above suggested formula, please change cell reference/ranges to suit Your requirement.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 2

Hi Amy A. Lotemplio:

According to your description, we speculate that you want to filter out some data in the workbook.

Since we are not sure about your specific data, we have made a simple model for your reference. If it does not match your actual situation, please correct me.

This model has repeated data in each column：

You can try to add filters to retrieve your data （Data-> Filter）：

Also You can use advanced filters to exclude duplicate data：

We appreciate your understanding that sometimes the initial suggestions may not resolve the problem very soon. However, we can work together to narrow down and resolve the situation. So, Please kindly provide more information below to let us work further:

• Screenshot of your data.
• Describe your needs in more detail, such as what data to filter or what data to get.

Hope this will help you.

Tin

Answer 3

Thank you for your help! The situation might be better described with the image I attached. In this example, I am wondering if there is a way to highlight the items that appear in all three columns (in this example, it would be apple). I am able to do this for items that appear more than once, using the duplicate function in conditional formatting- but I was wondering if there is a way to make excel highlight items that appear a specific number of times (3 times in this example). Thank you for your time!

Answer 4

Hi Amy

Please, try the following steps

1. Select the first column data range
2. Go to Conditional Formatting / New Rule/ Use a formula to determine ...

Use this formula and select the color formating of your preference

=ISNUMBER(MATCH($A2,$B:$B,0)*MATCH($A2,$C:$C,0))

3. Repeat the process for the other 2 columns ranges using the formulas

For column B

=ISNUMBER(MATCH($B2,$A:$A,0)*MATCH($B2,$C:$C,0))

For column C

=ISNUMBER(MATCH($C2,$B:$B,0)*MATCH($C2,$A:$A,0))

Results

Note:

Be aware of the columns ranges in your real scenario and adapt the formulas accordingly.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany