**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula to find outliers in columns A and B. Basically, we are looking at long lists of names that (in theory) are supposed to match. We are essentially cross checking two databases and making sure our customers are in both lists. Sometimes they only show up in one column or database, and I would like to find the outliers without having scroll through 1000 or so names and looking for extras. Is there a formula that can help me?

Solved by Z. H. in 23 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
08/04/2018 - 05:13

Yes

Excelchat Expert
08/04/2018 - 05:13

Hello there! :)

User
08/04/2018 - 05:13

I use 2016 and have been using Excel for about a year or so

Excelchat Expert
08/04/2018 - 05:14

Oh okay. :) Thanks for answering the auto questions. Do you have a working file we can work with during this session? :)

User
08/04/2018 - 05:14

No

User
08/04/2018 - 05:14

But I think I can describe the problem pretty well

Excelchat Expert
08/04/2018 - 05:15

Oh okay, sure then :)

User
08/04/2018 - 05:15

Nice

User
08/04/2018 - 05:15

I can show you on the example sheet right?

Excelchat Expert
08/04/2018 - 05:15

Yeah you can! :)

User
08/04/2018 - 05:16

Ok

User
08/04/2018 - 05:16

So I'm working with two databases with a long list of names and we basically cross check them to see if our records are good

User
08/04/2018 - 05:16

I will write a short example but the list is obviously longer

Excelchat Expert
08/04/2018 - 05:16

Oh okay. This two databases are in a single excel sheet?

Excelchat Expert
08/04/2018 - 05:17

Oh sure then, please do! :)

User
08/04/2018 - 05:17

We download the names and combine them on one sheet to make sure everyone matches up

User
08/04/2018 - 05:19

Ok

Excelchat Expert
08/04/2018 - 05:19

Nice! So you wish to compare column A and column B right?

User
08/04/2018 - 05:19

So this list is usually very long, but I need a formula that will signal to me when there are extras

User
08/04/2018 - 05:19

Yes

User
08/04/2018 - 05:19

I need to see the differences

Excelchat Expert
08/04/2018 - 05:21

I see. You want to know if the no. of Anthony's in column B exceed the no. of Anthony's in column A?

User
08/04/2018 - 05:22

Yes, I think that is what I want. If someone is only present in one column that means they are not active in one of our databases and that is a problem

Excelchat Expert
08/04/2018 - 05:22

Or could you provide a quick result of the given scenario in the document preview?

Excelchat Expert
08/04/2018 - 05:22

Oh okay!

User
08/04/2018 - 05:23

And I typed doubles because we tend to get similar names

Excelchat Expert
08/04/2018 - 05:23

Will it matter if there are 5 Anthony's in column B and only 4 Anthony's in column A?

User
08/04/2018 - 05:23

Yes

User
08/04/2018 - 05:23

But that happens less generally but it is important

User
08/04/2018 - 05:24

Column B tends to be the column where we have more people

User
08/04/2018 - 05:24

So if we get an Anthony Kim, I need to know that there is or isn't one in column B

Excelchat Expert
08/04/2018 - 05:25

okay then! Let me draft a formula for you :)

User
08/04/2018 - 05:25

Ok sweet

User
08/04/2018 - 05:26

Thank you

Excelchat Expert
08/04/2018 - 05:27

Okay, just made a formula in column C.

Excelchat Expert
08/04/2018 - 05:27

It tries to find in column A if there's a similar cell compared to column B.

User
08/04/2018 - 05:27

OK

User
08/04/2018 - 05:28

and I assume you could do the opposite for the other column?

Excelchat Expert
08/04/2018 - 05:28

yes we can! Let me do it for you :)

User
08/04/2018 - 05:28

Ok thank you!

User
08/04/2018 - 05:30

Just added a bit more time

Excelchat Expert
08/04/2018 - 05:30

Done! I've put it on column B to have them side by side. :)

Excelchat Expert
08/04/2018 - 05:30

Sure thing :)

Excelchat Expert
08/04/2018 - 05:32

I've put in column B the "signal" to whether there's a name in List 1 that doesn't match or matches a name in List 2

Excelchat Expert
08/04/2018 - 05:33

For example, the name "Hailey" in List 1 doesn't have a similar name in List 2. That's why it was tagged as "No match"

User
08/04/2018 - 05:33

Ok

User
08/04/2018 - 05:33

OK

User
08/04/2018 - 05:33

When you apply these formulas the columns should be next to eachother?

Excelchat Expert
08/04/2018 - 05:33

In List 2, the name "Johny" doesn't have a match in List 1, thus the "No match" signal. :)

Excelchat Expert
08/04/2018 - 05:33

not necessarily. :)

Excelchat Expert
08/04/2018 - 05:34

we can put them in a different column. We just need to ensure that the formulas are referenced correctly. :)

User
08/04/2018 - 05:34

Ok

User
08/04/2018 - 05:34

I will stick with the easy way for now

Excelchat Expert
08/04/2018 - 05:34

Oh sure thing! :)

User
08/04/2018 - 05:34

but this looks like the formulas I need!

User
08/04/2018 - 05:34

hanks

User
08/04/2018 - 05:34

Thanks

Excelchat Expert
08/04/2018 - 05:35

Sure thing! thanks as well for your time. you may end the session if all is well, and you may as well leave a good feedback lol! Have a nice day ahead! :)

Excelchat Expert
08/04/2018 - 05:36

should there still be questions, please feel free to ask! :)

User
08/04/2018 - 05:36

Ok

User
08/04/2018 - 05:36

I'm ending it!

User
08/04/2018 - 05:36

thanks

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*