comparing multiple columns

T

teejay

Hello All,

So I have about 7 columns of data that I want to compare with each
other and I want to find unique entries in each column as well as
common entries in more 2 or more than 2 columns.

What is the easiest and fastest method of doing it in excel.

Many thanks in advance-
 
T

Toppers

Look at Conditional Formatting:

Condition 1:
Highlight cells where count (use COUNTIF) in a given column is 1 AND count
(again use COUNTIF) in all columns is 1 i.e. unique to ONE Column only.

Condition2:

COUNTIF is 1 for selected column but >1 for ALL columns i.e. unique to a
given column but NOT unique among all columns.

etc

HTH
 
T

teejay

Can you please give some didactic instructions. I am not really good a
excel.

I checked conditional formatting option but couldn't find counti
option in any drop down menu!!!:confused
 
T

Toppers

Try this for a start (untested by me!)

Assume data is in columns A to G

Select column A ...

in CF:

1st Condition ..find values unique to column A ONLY i.e there is only one
occurrence in the 7 columns

Formula is: =AND(COUNTIF(A:A,A1)=1,COUNTIF(A:G,A1=1))

Set formatting .... GREEN

2nd Condition ..find values unique to column A but which occur in other
columns i.e. value is column A once but also in columns B:G

Formula is: =AND(COUNTIF(A:A,A1)=1,COUNTIF(A:G,A1>1))

Set formatting ... YELLOW

If this works, repeat for other columns change references to A to B,C etc.

This could get rather messy and not easy to interpret but comparing 7
columns is unusual.

HTH
 
Top