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.
