Pull out duplicate values

[john_prince]

Hi,

I've two columns containing email addresses, first column is the master column and it contains everything, second column contain lesser number of emaild, and my intention is to know the difference between first and second.

1|1 output should be 3 & 6.
2|2
3|4
4|5
5|7
6
7

Please help me how can i do this.

Thanks, John

[rasonline]

you could use a MATCH formula
see attachment.

[somesoldiers]

Hi John

see attached- is this what you want?

[john_prince]

Hi Rasonline,

The file works for couple of rows, but when i put 100 email addresses, it is not working. Where do i need to change it?

Thanks, John

[john_prince]

Hi somesoldiers,

The same issue with your file as well.

Thanks

[somesoldiers]

you'll have to post an example, delete sensitive data as you see fit

[JBeaucaire]

Here's a way.... Just drop your lists into columns B and C, and extend the formulas down columns A and G as needed.

[john_prince]

Hi JBeaucaire & Somesoldiers,

It is not working, i've attached my lists in in the file.

Thanks, John

[somesoldiers]

why didn't my original solution work for you please see attached,looks good with the sample data, maybe there is something wrong with your actual data, ie an address on the main sheet is not exactly what's on the second- what sort of error are you seeing?

[JBeaucaire]

Sorry, John, I had the column A key backwards. change A2 to this and copy down:

=IF(NOT(ISNUMBER(MATCH(B2,C:C, 0))), N(A1)+1, N(A1))


I've corrected the file in the post #7 above.

[martindwilson]

hey somesoldiers try to use true/false not 1/0 in vlookup as that's what the help files show, it's less confusing that way for others

[somesoldiers]

Personally, I've never come across a situation where it made a blind bit of difference (til now obviously) but I bow to your superior knowledge and will desist from misleading folk immediately. Incidentally, are new users encouraged to try to, purely altruistically, help where they think they can or would you guys prefer us to sit on the side with the half time oranges and the overweight school boys and leave the responding to the forum's old timers?

[JBeaucaire]

SomeSoldiers....

1) If you're going to be sarcastic, sprinkle in some "chuckles" and "hehe"s to keep the sarcasm from making you sound like a jerk. This isn't meant as an attack, just a reminder that text onscreen has no character whatsoever, so everything reads as harsh...

Joking aside, if someone says something you disagree with, then disagree...without the condescension if you can. ("I bow to your superior knowledge") isn't nearly as interactive as "I understand your point and disagree that's an important one" or even "I don't see that as an issue. Can you provide examples of how it is an actual problem?"

On the VLOOKUP() thing, I use 0 and 1 and point out the meaning when necessary. Martin's opinion notwithstanding, it's fine. Just be ready to fully explain whatever you do/offer.


2) You darn well should dive in. The fastest way to insure you really know what you think you know is to try and solve other's issues.

You will offer suggestions and some will be good, some will be bad, some will be downright silly when you see the final solutions offered, but overall, the point of the forum is multi-way interaction. Even as you try to teach, you'll learn all the more. What a great benefit.

"None of us is a good as all of us." - Ray Kroc (words to live by)

[martindwilson]

.i just think if someones new to vlook up then they can check on whats offered here then look at the help files and see what's happening.

[john_prince]

Sorry, John, I had the column A key backwards. change A2 to this and copy down:

=IF(NOT(ISNUMBER(MATCH(B2,C:C, 0))), N(A1)+1, N(A1))


I've corrected the file in the post #7 above.

Hi JBeaucaire,

your solution works. Thanks a lot. ( #7)

I would like to thank somesoldier for all his efforts as well. Have a good day.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.