?
Solved

@If @Vlookup ?????

Posted on 2003-03-12
9
Medium Priority
?
285 Views
Last Modified: 2012-05-04

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
Comment
Question by:rwebsteris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 45

Accepted Solution

by:
patrickab earned 1000 total points
ID: 8126814
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8126819
Apologies the formula should have been:

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

0
 
LVL 45

Expert Comment

by:patrickab
ID: 8127106
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rwebsteris
ID: 8128244
Hi patrickkab,

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

Is there some way of having the name copied into column E.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 8132416
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
 

Author Comment

by:rwebsteris
ID: 8132455
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8134809
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
 

Author Comment

by:rwebsteris
ID: 8136053
Thanks ever so much for all your assistance.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 8136917
My pleasure - thanks for the A grade
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question