match up column A with column B

M

Micronaut

Hi,

I have a column of numbers in A, and a column of numbers in B. There
are 180 numbers in A (A1:A180) and 130 numbers in B (B1:B130). I need
Excel to find which numbers match up between columns, e.g look down
column A and find all the matching numbers in column B, then sort the
columns so the matched numbers are next to each other (so I can see
which numbers don't match between columns). (I have sorted the numbers
from lowest to highest down the column, so they are in numerical
order).

I have no idea how to do this.
:confused:

Thanks for any assistance you can provide. :)
 
S

swatsp0p

Here's one way:

Insert a blank column B (current B becomes C)

In B1, enter this formula:

=IF(ISERROR(VLOOKUP(A1,$C$1:$C$130,1,0)),"",VLOOKUP(A1,$C$1:$C$130,1,0))

and copy this formula down to B180

Column B will be blank if the value in A is not found. If the value in
A is found, the matching entry will be returned.

Will this work for you?
 
M

Micronaut

swatsp0p said:
Here's one way:

Insert a blank column B (current B becomes C)

In B1, enter this formula:

=IF(ISERROR(VLOOKUP(A1,$C$1:$C$130,1,0)),"",VLOOKUP(A1,$C$1:$C$130,1,0))

and copy this formula down to B180

Column B will be blank if the value in A is not found. If the value in
A is found, the matching entry will be returned.

Will this work for you?

swatsp0p,

Thanks for the reply, that's worked well. Is there any way to
highlight in the now column C, which numbers didn't match up?
:)
 
S

swatsp0p

In C1, use Format>Conditional Formatting as such:

Choose Formula Is: =ISERROR(MATCH(C1,$B$1:$B$180,0)) and set
Format>Patterns to your desired fill color (I like Rose :) ) to
highlight cells that don't match in Col. B

Then copy this cell (C1) and Paste Special>Formats in range C2:C130

Does this work for you?
 
M

Micronaut

swatsp0p,

Yes, that also worked well. I tried using the original formula as an
additional conditional format to highlight the matches displayed in
column B, but it's not highleted them all. There weren't many "hits"
from column B so I sorted them manually from the list generated from
the original formula.

Thanks for your help with the problem, was much appreciated. Sorted my
problem, literally! :)
 
S

swatsp0p

I'm glad this worked for you. Thanks for the feedback, it is always
appreciated.

Cheers!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top