If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Comparing data between two columns
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 |
#3
|
|||
|
|||
Comparing data between two columns
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 |
#4
|
|||
|
|||
Comparing data between two columns
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 |
#5
|
|||
|
|||
Comparing data between two columns
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 |
Thread Tools | |
Display Modes | |
|
|