Dear all,

I wanted to find the numbers of column B which are present in Column A?

Could any one provode the formula.

A B
201 35
211 53
21254 212
22 3256
35 201
25 635
53 656


Best Regards,
Tej

Can't you do it ?

Quote:

---

Originally Posted by tejutej

Dear all,

I wanted to find the numbers of column B which are present in Column A?

Could any one provode the formula.

A B
201 35
211 53
21254 212
22 3256
35 201
25 635
53 656


Best Regards,
Tej

---

Dear Tickle,

Please note that, just I have given a small example. I have a spreadsheet with more that 5000 rows...
Please help me in this regard.

Kind Reagrds,
Tej

Why was my answer not accurate. Why do you need help doing this exercise? Can't you do it yourself ? I don't think that 'it is not accurate' is a proper response to my question.

How do you want to check? One number at a time for B? How do you want to show the answer? One answer each?
For example:

Code:

---

A      B      C
201    35    no
211    53    no
21254  212    no
22    3256  no
35    201    yes
25    635    no
53    656    no

---

Would that suffice?

Thanks a lot!

I wanted the numbers which are present in both column A as well as column B.

I also want the numbers which are present in column B but not in Column A.

Could this is possible? If yes Please provide the instructions.

Kind Regards,
Tej

You'll need to do this in two steps. The formula you want, based on your example, is:

Code:

---

=IF(ISERROR(VLOOKUP(B1,$A$1:$B$7,2,FALSE)),"no","yes")

---

(see the picture below)

After you enter the formula and copy it into column C for all entries, you'll have yes/no for each line. Now highlight ALL THREE columns, and SORT by column C (see the second part of the pic below). Now all the yes answers are together and you have all the numbers grouped.

Another satisfied customer... chachacha...