Excat Forumla HELP
 

I have a file with 5 columns in my file with 3000 lines and was given another
file with about 3500 lines.

I need to be able to compare these 5 colums including the location with the
other file. If the 5 colums match exactly with the other file I want a value
of YES. When I put in the formula if it matches with Column A from my file
with the Column A in the other file it says yes. I want it to match Column A,
B, C, D, E with A,B,C,D,E from the orher file.

I've read a bit about "vlookup" but as I don't understand the syntax of the
formula, I don't know if this will work. Especially since the lists are not
even.I just need to generate a report of identical item numbers.

Is there a way to do what I need to do?

EXAMPLE:

MY SHEET: OTHER SHEET:

Column A B C D E Column A B
C D E
Pen Yellow Ora Borad Chicago Orange Pink Fly
Map Los Ang
Orange Pink Fly Map Los Angeles Pear Tie red
kite Chicago
Apple Red Okay Moon New York Apple Red Okay Moon
Chicago

Now i want all of the 5 colums from my sheet to be cheaked by the other 5 in
the other sheet. If they all match including the location i want it to say
yes. If 4 coulms match and the location does not match i want it to say No
Value or whatever.

--
Thnx Siddiqui

Hi!

Try one of these:

Array entered with the key combo of CTRL,SHIFT,ENTER:

=EXACT(A1:E1,[File2]Sheet1!A1:E1)

Or, a non-array version of the same thing:

=EXACT(A1&B1&C1&D1&E1,[File2]Sheet!1A1&B1&C1&D1&E1)

These will return either TRUE - an exact match, or FALSE -
not an exact match.

NB - exact means just that, EXACT

Biff

-----Original Message-----
I have a file with 5 columns in my file with 3000 lines
and was given another
file with about 3500 lines.

I need to be able to compare these 5 colums including the
location with the
other file. If the 5 colums match exactly with the other
file I want a value
of YES. When I put in the formula if it matches with
Column A from my file
with the Column A in the other file it says yes. I want
it to match Column A,
B, C, D, E with A,B,C,D,E from the orher file.

I've read a bit about "vlookup" but as I don't understand
the syntax of the
formula, I don't know if this will work. Especially since
the lists are not
even.I just need to generate a report of identical item
numbers.

Is there a way to do what I need to do?

EXAMPLE:

MY SHEET:
OTHER SHEET:

Column A B C D E
Column A B
C D E
Pen Yellow Ora Borad Chicago
Orange Pink Fly
Map Los Ang
Orange Pink Fly Map Los Angeles
Pear Tie red
kite Chicago
Apple Red Okay Moon New York Apple
Red Okay Moon
Chicago

Now i want all of the 5 colums from my sheet to be
cheaked by the other 5 in
the other sheet. If they all match including the location
i want it to say
yes. If 4 coulms match and the location does not match i
want it to say No
Value or whatever.

--
Thnx Siddiqui
.

This is not working.
Thanks anyways

"Biff" wrote:

Hi!

Try one of these:

Array entered with the key combo of CTRL,SHIFT,ENTER:

=EXACT(A1:E1,[File2]Sheet1!A1:E1)

Or, a non-array version of the same thing:

=EXACT(A1&B1&C1&D1&E1,[File2]Sheet!1A1&B1&C1&D1&E1)

These will return either TRUE - an exact match, or FALSE -
not an exact match.

NB - exact means just that, EXACT

Biff

-----Original Message-----
I have a file with 5 columns in my file with 3000 lines
and was given another
file with about 3500 lines.

I need to be able to compare these 5 colums including the
location with the
other file. If the 5 colums match exactly with the other
file I want a value
of YES. When I put in the formula if it matches with
Column A from my file
with the Column A in the other file it says yes. I want
it to match Column A,
B, C, D, E with A,B,C,D,E from the orher file.

I've read a bit about "vlookup" but as I don't understand
the syntax of the
formula, I don't know if this will work. Especially since
the lists are not
even.I just need to generate a report of identical item
numbers.

Is there a way to do what I need to do?

EXAMPLE:

MY SHEET:
OTHER SHEET:

Column A B C D E
Column A B
C D E
Pen Yellow Ora Borad Chicago
Orange Pink Fly
Map Los Ang
Orange Pink Fly Map Los Angeles
Pear Tie red
kite Chicago
Apple Red Okay Moon New York Apple
Red Okay Moon
Chicago

Now i want all of the 5 colums from my sheet to be
cheaked by the other 5 in
the other sheet. If they all match including the location
i want it to say
yes. If 4 coulms match and the location does not match i
want it to say No
Value or whatever.

--
Thnx Siddiqui
.