Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

@If @Vlookup ?????


Hi there:

I have a 123 spreadsheet with 2 lists, I need to extract from list B the names that are NOT in List A .

                  Any Ideas

                  Renee Webster
0
rwebsteris
Asked:
rwebsteris
  • 6
  • 3
1 Solution
 
patrickabCommented:
Table named LISTA
chg
dgfnh
dsb
fgf
fjhgk
fjm
fredwdlfj
retj

Make LISTA a named range two columns wide although column 2 will have nothing in it.

LISTB
cvbn
gfshfg
nbvn
retj
fgf
vb
vcxfg
xcbn

In the column after LISTB (assuming it's in column E)put the following formula:

@IF(@ISERR(@VLOOKUP(E1,$LISTA,1)),C1,"")

copy it down the column and it will only list those items not in LISTA.

Hope that does what you want - please ask if my explanation is a bit cryptic!

0
 
patrickabCommented:
Apologies the formula should have been:

@IF(@ISERR(@VLOOKUP(E1,$LISTA,1)),E1,"")

0
 
patrickabCommented:
By the way if you have names split into 2 column e.g. Renee in column A and Webster in column B it would be better to join them together by using a 3rd column with the following formula in it +A1&B1. With this formuala you will get ReneeWebster in the 3rd column. Then you can put that column into LISTA and use a similar approach to LISTB - so that you are comparing like with like. At least that way you will search for the complete name not just the given name nor just the family name.

hth
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rwebsterisAuthor Commented:
Hi patrickkab,

Seems to work OK except that it returns "0".

Is there some way of having the name copied into column E.
0
 
patrickabCommented:
I have just re-tried it for myself at home and it does work. So I now need from you some information to be able to figure out what is happening and why it isn't working for you. Can you let me know:

1. The name of the range for the first set of data. You originally called it LISTA in the question

2. The column in which you have the second list - you originally called this LISTB - although it does not need a range name for my solution to work.

By the way for the @vlookup() to work you do need to sort LISTA in ascending order on the first column in the range LISTA.

The other thing is that the list of names you get using the @vlookup() will need to be copied and paste/special/values to get just the values and not the formulae.

It's 11:15pm here so if I don't get your response this evening I will have a look at it tomorrow.

Meanwhile I hope this helps.
0
 
rwebsterisAuthor Commented:
Hi patrickab;

thanks for getting back to me. I am at home now and do not have 123 on my home computer. I will take a closer look at it in the morning when I get to work. I am in the Toronto area. EST. I usually arive in the office @ 7:30 which if my calcs are correct would make it 12:30 your time.

Again thanks so much for the assistance.

Renee Webster
0
 
patrickabCommented:
Chere Renee,

That should give us a window of opportunity of about 5hrs to sort this out today. If you would like to send me a spreadsheet as an example (it can use dummy data) that might speed up the whole process. My email address is patrick.arnold-baker@jaeger.co.uk

Hope to hear from you soon.

Patrick
0
 
rwebsterisAuthor Commented:
Thanks ever so much for all your assistance.
0
 
patrickabCommented:
My pleasure - thanks for the A grade
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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