Simple question.

Hi,
This looks like a simple problem, but I have not been able to figure it out. I need to compare 2 columns of data containing the same data. Both columns would contain both numbers and alphabhets. The 2nd column would NOT have all the rows in the first column.

I would need to identify which cell in A column does not have a comparable value in B column. In other words I would like to identify records which appear in both columns.


Thanks in Advance

Originally Posted by georger

Hi,
This looks like a simple problem, but I have not been able to figure it out. I need to compare 2 columns of data containing the same data. Both columns would contain both numbers and alphabhets. The 2nd column would NOT have all the rows in the first column.

I would need to identify which cell in A column does not have a comparable value in B column. In other words I would like to identify records which appear in both columns.


Thanks in Advance

Hi georger,

What you are trying to do is to create a unique list? With your data in columns A and B, put this in C1

=IF(ISERROR(MATCH(B1,$A$1:$A$1000,0)),B1,"")

then drag down, this will make a list of items in column B that are not in column A, if they are then a blank cell is created.

oldchippy

Old chippy. Really appreciate your answer. I put the formula in and it did not work. I guess I did not clearly state the problem. I have a specific ID number on A. This ID corresponds to a number in column B on the same row. Column C also would have the same number as that of column B. But there will be some missing numbers in Column C. So what I need to find is which number in column C also appears in Column B or vice versa (which number in column B does not have a corresponding number in Column C)



column a column b Column c
106823 018919829 018919829
146715 018919860 018919860
64285 067091108 124676107
64402 126375302 141410209
30542 141410209 17306J202
3661 17306J202 196877104
66981 03634M109
210419 124676107
17546 196877104
64901 282485101


Thanks again

Originally Posted by georger

Old chippy. Really appreciate your answer. I put the formula in and it did not work. I guess I did not clearly state the problem. I have a specific ID number on A. This ID corresponds to a number in column B on the same row. Column C also would have the same number as that of column B. But there will be some missing numbers in Column C. So what I need to find is which number in column C also appears in Column B or vice versa (which number in column B does not have a corresponding number in Column C)



column a column b Column c
106823 018919829 018919829
146715 018919860 018919860
64285 067091108 124676107
64402 126375302 141410209
30542 141410209 17306J202
3661 17306J202 196877104
66981 03634M109
210419 124676107
17546 196877104
64901 282485101


Thanks again

Try this in D1

=IF(ISERROR(MATCH(C1,$B$1:$B$1000,0)),C1,"")

Hmnn. That did not work either. For eg 124676107 (which appears both in B & C shows a zero value in D. But 282485101 also shows a zero value in D(This appears only in B).

Originally Posted by georger

Hmnn. That did not work either. For eg 124676107 (which appears both in B & C shows a zero value in D. But 282485101 also shows a zero value in D(This appears only in B).

Hmmmmm, well the 124676107 doesn't show a zero in mine, but yes 282485101 will show a zero because the list of items in col C are shorter, if it was the same length as col B then it would be blank if it have found the corresponding number in col B

Okay. I was referring to column A. Old Chippy- Thanks for your quick response on this. . Do you think is there any other way I can find a solution. Since in real life I am running comparisons for >10000 rows. Again the question is- A is the common identifier for B & C. B & C comes from different sources. I just need to find the identifier (A) for which there is a value in B, but does not have the identical value in C. My point here is that there will be always missing values in the C. So C always would be shorter since there are always missing values in C.

Originally Posted by georger

Okay. I was referring to column A. Old Chippy- Thanks for your quick response on this. . Do you think is there any other way I can find a solution. Since in real life I am running comparisons for >10000 rows. Again the question is- A is the common identifier for B & C. B & C comes from different sources. I just need to find the identifier (A) for which there is a value in B, but does not have the identical value in C. My point here is that there will be always missing values in the C. So C always would be shorter since there are always missing values in C.

Hi georger,

I'm trying to grasp your problem, if a value appears in column B against the identifier in column A, then you want to know whether the value in column C = Column B? If this is the case, then in column D

=IF(B1=C1,"",C1)

Sorry I am not clear on the question. I guess this would have worked if 2 columns which are being compared has the same number of rows. The one thing I need to point out is A & B rows are related ie whatever is in A1 is related to B1. Having said that my problem is that C column only will contain about half of the data which comes in column B. And that data could come in any row in B. So I would like to identify those cells. The net result I like to get from running the formula in the example is to identify the following the Column A numbers.




64285
64402
66981
64901
That is for all the numbers in Column A, there are corresponding numbers in B, but no number in any row of C. Thanks...


For eg 64285(a) there is a value in B (067091108), but this value does not appear in C

Originally Posted by georger

Sorry I am not clear on the question. I guess this would have worked if 2 columns which are being compared has the same number of rows. The one thing I need to point out is A & B rows are related ie whatever is in A1 is related to B1. Having said that my problem is that C column only will contain about half of the data which comes in column B. And that data could come in any row in B. So I would like to identify those cells. The net result I like to get from running the formula in the example is to identify the following the Column A numbers.




64285
64402
66981
64901
That is for all the numbers in Column A, there are corresponding numbers in B, but no number in any row of C. Thanks...


For eg 64285(a) there is a value in B (067091108), but this value does not appear in C

Right, I think we've got it now, a minor adjustment to the formula will select the numbers in column A that have a number in column B that is not matching a number in any row of column C. Put this in column D.

=IF(ISERROR(MATCH(B1,$C$1:$C$1000,0)),A1,"")

Is this OK now?

Originally Posted by Bryan Hessey

Hi oldchippy,

you might be better off to switch that the other way, ignore the IsError and list the match with
=IF(ISERROR(MATCH(B1,C:C,0)),"",B1) this will then, in effect, re-align column C to A-B (in D), then a Paste Special Values will allow the removal of column C.

A CountA of column D and column C will show whether there were items in column C that were not in column B, if there were then a reverse match should find those, but probability says there will be none.


Hope this helps.
---

Cheers Brian,

Another twist that's leaning in the opposite direction again