[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

I need to See which numbers are missing from two lists in excel

I have a master list of numbers. then i have a list of numbers from barb and from cathy. How can i figure out which numbers are in Barb's list but not Cathy's and which are in Cathy's but not Barbs

I have attached a sample here. I think you would use vlookup?

Thanks!
Comparisons.xls
0
jtovar3
Asked:
jtovar3
  • 2
1 Solution
 
abitounCommented:
the following formula works for:
column D
=IF(AND(IFERROR(VLOOKUP(C4,B:B,1,FALSE),"*")<>"*",IFERROR(VLOOKUP(C4,A:A,1,FALSE),"*")<>"*"),"Both","")
column E
=IF(AND(IFERROR(VLOOKUP(C4,B:B,1,FALSE),"*")="*",IFERROR(VLOOKUP(C4,A:A,1,FALSE),"*")<>"*"),"Barb's","")
column F
=IF(AND(IFERROR(VLOOKUP(C4,B:B,1,FALSE),"*")<>"*",IFERROR(VLOOKUP(C4,A:A,1,FALSE),"*")="*"),"Cathy's","")
but i see that you have some values stored as text and some as numbers and this might not work, also not all the values are the same length correct?
0
 
abitounCommented:
if all Cathy's are text then this will work:
Column D
=IF(AND(IFERROR(MATCH((C7&""),B:B,),"*")<>"*",IFERROR(MATCH(C7,A:A,),"*")<>"*"),"Both","")
Column E
=IF(AND(IFERROR(MATCH((C7&""),B:B,),"*")="*",IFERROR(MATCH(C7,A:A,),"*")<>"*"),"Cathy's","")
Column F
=IF(AND(IFERROR(MATCH((C7&""),B:B,),"*")<>"*",IFERROR(MATCH(C7,A:A,),"*")="*"),"Barb's","")

if not make sure that all three columns are text or all theree are numbers

see this link http://www.contextures.com/xlDataEntry03.html
0
 
jtovar3Author Commented:
Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now