Looking for matches between columns

[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!

 

[Bernie Deitrick]

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

 

[F. Belvoir]

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!

 

[Bernie Deitrick]

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

 

[Guest]

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).