match up column A with column B

  • Thread starter Thread starter Micronaut
  • Start date Start date
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. :)
 
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 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?
Click to expand...

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?
:)
 
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?
 
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! :)
 
I'm glad this worked for you. Thanks for the feedback, it is always
appreciated.

Cheers!
 
Back
Top