Looking for matches between columns

  • Thread starter Thread starter F. Belvoir
  • Start date Start date
F

F. Belvoir

I'm not an Excel guru but I'm good with directions--- could someone point me
in the right direction?

I have a spreadsheet with 6 columns of data that I would like to compare to
a 7th column. If a match for any cell in Column 7 is found anywhere in
Columns 1-6, I don't want to be notified. If there is no match, I'd like a
new line written. I need the disparities to be noted if they appear only
within one column--- for example, if cell in Column 7 matches cell in Column
1,2,4,5,6 but not 3, I would like a line written. Anytime the match is not
found anywhere in one particular column against my baseline/Column 7, I want
an entry made.

I think I need vlookup for this but my questions are:

-Do I need to define each as an array and start a new column for each
possible output entry?
-How to tell where the differences entry comes from? Meaning, if I have an
entry written, how can I know if it's there because the match was not found
in Column 2 as opposed to Column 4?

I think I need a macro, so I can run it on demand. Do I need 6 of them?

Thanks in advance!
 
Let's say that you data is in columns A:F, and your 7th column is col G. In cell H2 (I assume you
have headers), enter the formula

=NOT(ISERROR(MATCH(A2,$G:$G,False)))

and copy down column H to match columsn A through F, then copy all of column H to I:M.

Any FALSE values will show missing values from the corresponding row, offset by 7 columns. (A False
in cell I23 means that B23 was not matched...)

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie. Any way to do it so the value does not have to be on the
corresponding row? In other words, I want to find a match for A23 anywhere
in B.
Or am I being too hopeful here?

TIA!
 
F.,

The MATCH function looks for one value anywhere within another range, and returns a number if it is
found (the index, or row, depending on the selected range) and an error if it is not found.

So,

=MATCH(A23,B:B,False)

will look for an exact match in column B for the value in A23, and return the row in column B where
it is found (if it is found...).

Does that help?

HTH,
Bernie
MS Excel MVP
 
Two possibilities:

1. (Which I think is faster, though I'm a programmer.) Write a macro that
simply loops through the range of cells in the 6 columns and compares the
current cell in column 7 to the cells in the 6 columns. If no match is found
in any column, it puts that column number into a string and at the end of the
search writes that string to an adjacent cell. So the output would look
something like this:

A B C D E F G H (H column is the column to report the misses)
A, D <-- this string gets output by the macro
saying the value in G (whatever that happens to be) was not found in columns
A and D.

2. VLOOKUP - this would take 6 of them, 1 per column. So using the above
picture, columns H-M would each contain a lookup of the value in column G (H
would check A, I - B, etc.). You can wrap it with an IF statement so the
cell appears blank if it found a match and the column letter if it doesn't.
Here's the formula for H:

=IF(ISNA(VLOOKUP(G1,A$1:A$100,1,FALSE)),"A","")

Copy this formula to I-M and down as many rows as needed (of course change
the 100 to be however many rows you need).
 
Back
Top