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:

May I know whether there are any updates to your question? If you have any questions, please feel free to contact us.

Tin