Comparing data between two columns

I have a worksheet with two columns that I need to compare data between. I
need to see what is different between one column and the next. There are
number and letter values in the column and about 180+ rows. The information
is scattered throughout the cells so there is no way to filter and check row
by row.

The columns look like this:

July April
MP123456789 MP555555559
MP987654321 MP666 H1258
MP789456123 MP00000 H89
MP987123654 MP123456789

I need something that would tell me that all of the above values are unique
between the two columns except for MP123456789

Hi:

You can try the countif function and if there are none then it is unique

ie if the codes are in columns a and c and then put the formuae in the
columns b and d.
A B C D
MP123456789 =countif(c:c,a1) MP555555559 =countif(a:a,c1)
MP987654321 =countif(c:c,a2) MP666 H1258 =countif(a:a,c2)
MP789456123 =countif(c:c,a3) MP00000 H89 =countif(a:a,c3)
MP987123654 =countif(c:c,a4) MP123456789 =countif(a:a,c4)

Where there is a number greater than zero means that there is a match.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"ABeezy" wrote:

I have a worksheet with two columns that I need to compare data between. I
need to see what is different between one column and the next. There are
number and letter values in the column and about 180+ rows. The information
is scattered throughout the cells so there is no way to filter and check row
by row.

The columns look like this:

July April
MP123456789 MP555555559
MP987654321 MP666 H1258
MP789456123 MP00000 H89
MP987123654 MP123456789

I need something that would tell me that all of the above values are unique
between the two columns except for MP123456789

You can highlight the unique values by Conditional Formatting as under:

1. Select both the columns,
2. Click on Conditional Formating under Home Tab
3. Select New Rules and select "Format only Unique and duplicate values"
then select Unique from drop-down box and select formating as you desire.

Hope this will work for you.
rgds...

"ABeezy" wrote:

I have a worksheet with two columns that I need to compare data between. I
need to see what is different between one column and the next. There are
number and letter values in the column and about 180+ rows. The information
is scattered throughout the cells so there is no way to filter and check row
by row.

The columns look like this:

July April
MP123456789 MP555555559
MP987654321 MP666 H1258
MP789456123 MP00000 H89
MP987123654 MP123456789

I need something that would tell me that all of the above values are unique
between the two columns except for MP123456789

Thank you so much! This made it super easy to find unique values!

"Excel_Learner" wrote:

You can highlight the unique values by Conditional Formatting as under:

1. Select both the columns,
2. Click on Conditional Formating under Home Tab
3. Select New Rules and select "Format only Unique and duplicate values"
then select Unique from drop-down box and select formating as you desire.

Hope this will work for you.
rgds...

"ABeezy" wrote:

I have a worksheet with two columns that I need to compare data between. I
need to see what is different between one column and the next. There are
number and letter values in the column and about 180+ rows. The information
is scattered throughout the cells so there is no way to filter and check row
by row.

The columns look like this:

July April
MP123456789 MP555555559
MP987654321 MP666 H1258
MP789456123 MP00000 H89
MP987123654 MP123456789

I need something that would tell me that all of the above values are unique
between the two columns except for MP123456789

Thank you! This is exactly what I was looking for.

"Martin Fishlock" wrote:

Hi:

You can try the countif function and if there are none then it is unique

ie if the codes are in columns a and c and then put the formuae in the
columns b and d.
A B C D
MP123456789 =countif(c:c,a1) MP555555559 =countif(a:a,c1)
MP987654321 =countif(c:c,a2) MP666 H1258 =countif(a:a,c2)
MP789456123 =countif(c:c,a3) MP00000 H89 =countif(a:a,c3)
MP987123654 =countif(c:c,a4) MP123456789 =countif(a:a,c4)

Where there is a number greater than zero means that there is a match.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"ABeezy" wrote:

I have a worksheet with two columns that I need to compare data between. I
need to see what is different between one column and the next. There are
number and letter values in the column and about 180+ rows. The information
is scattered throughout the cells so there is no way to filter and check row
by row.

The columns look like this:

July April
MP123456789 MP555555559
MP987654321 MP666 H1258
MP789456123 MP00000 H89
MP987123654 MP123456789

I need something that would tell me that all of the above values are unique
between the two columns except for MP123456789