Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forumla to calculate a percentage | Excel Discussion (Misc queries) |