comparing data

[sjp1966]

Hi

I wonder if someone could advise if the following is possible.



I have an excel sheet that has 2 columns of data. The data is from different sources but will always consist of email addresses.

I want to compare column A with Column B to highlight any duplicate email addresses that may show over both columns.

Is this possible and if so how do I do it.

Thanks

Steve

[shilabrow]

Hi Steve, This formula should help you. Create a third column and insert formula in C2 assuming A1 and B1 are titles and drag down or double click small + sign on right end of the formula cell to paste down . What the formula below is saying is, look at Column A and compare with individual cell in copy B, if any duplicate "Duplicate" and if not "No". I hope this helps.

=IF(COUNTIF($A:$A,B2)>=1,"Duplicate","No")

[sjp1966]

Hi Shilabrow

I think it is partially working. I am assuming that the B2 portion of your formula looks at the data in B2 and compare it to the whole column?

Thing is one column is 923 lines of names, and I need to compare a list that is 304 lines

So say A2 onwards is 923 names, B2 onwards is 304 names, I need column C to say are any of the 304 names appearing in the list of 923 names? (not sure if that makes sense, I think the formula so far (unless I am using it incorrectly) is looking at one name at a time.

[shilabrow]

sjp1966, I am confused because from your request the formula should work. All duplicates that come out in Cell C are those entries that are 304 names while all NO that it spills out are those part of 923 names in Column A that are not in Column B.

Note, the B2 portion of the Formula above looks at Column A and gives result.

OR since Column A is 923 lines and Column B is 304, use as below and copy down.

=IF(COUNTIF($B:$B,A2)>=1,"Duplicate","No")

hope this works. All duplicate are 304 and all No are others...

[sjp1966]

Hmmm, ok ill give it another go.

When I did it the column that should show duplicate or no only had no in it.

So I added my name at the bottom of each column (a and b) and low and behold duplicate appeared next to my name.

I then changed one of the names at the top of column A to match a random name in column B, the no didn't change to duplicate, I reran the query (using the drag cell option) and the column still said no.

[gebobs]

It should work. I've reproduced shilabrow's solution attached here.

[sjp1966]

Yep it worked, I entered the code in again... not sure why it didnt the first time, a typo from me perhaps.

thanks guys

Steve