Compare Data Problem (using if / vlookup?)

[AlvinGP]

Hi - can anyone help me?
I want to compare two sets of data, A and B. A is large data set, and B contains the numbers that i want to find in A and hi lite them, and have the rest deleted. How can i do it with formula?

For example, as below i just want to hi lite only 6534 and 3456. Please help

A B
1234 6534
4214 3456
1236
6534
5678
3456
7578

 

[mvptomlinson]

Hi Alvin, you can use Conditional Formatting to highlight the matching values.

Select the values in column A, click Conditional Formatting > New Rule > Use formula to determine...

Use the following formula, then set your Format (Fill color, font color, etc.):

=ISNUMBER(MATCH(A1,$B$1:$B$10,0))

You cannot delete cells using formulas, you would need a macro to do that. (Or use filters to hide them.)

 

[T. Valko]

Hi - can anyone help me?
I want to compare two sets of data, A and B. A is large data set, and B contains the numbers that i want to find in A and hi lite them, and have the rest deleted. How can i do it with formula?

For example, as below i just want to hi lite only 6534 and 3456. Please help

A B
1234 6534
4214 3456
1236
6534
5678
3456
7578

You can highlight the matches using a formula in conditional formatting but you can't delete the non-matches with a formula.

What version of Excel are you using?

 

[AlvinGP]

Mr. mvptomlinson - thanks a lot. I tried and it works. The problem is as you said, i cannot delete it, and it wont work if B column were from other worksheet/book. Is there other way to solve this?

Actually column A is just part of bigger data which relates to other information in the same spreadsheet (other columns). And i need whole information, not just column A.

would appreciate any other comments. Thanks