Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Multi-Columns of Data in Different Files
hi,
I have 2 files, Files A (old) contained data in below order:- Brand Type Unit Flag AA 123 2 Yes AB 234 No No In File B, data are organized in below order:- Brand Type Unit Flag AA 123 2 AB 234 3 CC 135 5 What I would like to know is how to compare each value in Brand, Type & Unit whether they are the same in each file; if they are the same then the Flag value in File A will be returned; else No will be retruned in File B. Actually, I have tried out using Vlookup functions but unfortunately, this function can only compare data in 1 column. So, is there any other functions that can help in solving this problem? Thanking in advanced. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Multi-Columns of Data in Different Files
in this case an extra column will help you to use vlookup as follow:
file A A B C D E 1 Brand Type Unit Flag extra column 2 AA 123 2 Yes =A1&B1&C1 3 AB 234 No 4 No file B A B C D E Brand Type Unit Flag 1 AA 123 2 =vlookup(E1,fileA!E1:E?,1,false) =A1&B1&C1 2 AB 234 3 3 CC 135 5 Hope it works -- Farhad Hodjat "Jac" wrote: hi, I have 2 files, Files A (old) contained data in below order:- Brand Type Unit Flag AA 123 2 Yes AB 234 No No In File B, data are organized in below order:- Brand Type Unit Flag AA 123 2 AB 234 3 CC 135 5 What I would like to know is how to compare each value in Brand, Type & Unit whether they are the same in each file; if they are the same then the Flag value in File A will be returned; else No will be retruned in File B. Actually, I have tried out using Vlookup functions but unfortunately, this function can only compare data in 1 column. So, is there any other functions that can help in solving this problem? Thanking in advanced. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Multi-Columns of Data in Different Files
Hi, Farhad
Thanks for your reply! The formula does work but the values in E column are being returned to Flag clolumn instead of the Flag values. Plus, by using FALSE as the range_lookup value, once the data does not matched; an error message will be returned instead of No values from Flag column in File A. is there any other functions can help to solve these? Thanks! "Farhad" wrote: in this case an extra column will help you to use vlookup as follow: file A A B C D E 1 Brand Type Unit Flag extra column 2 AA 123 2 Yes =A1&B1&C1 3 AB 234 No 4 No file B A B C D E Brand Type Unit Flag 1 AA 123 2 =vlookup(E1,fileA!E1:E?,1,false) =A1&B1&C1 2 AB 234 3 3 CC 135 5 Hope it works -- Farhad Hodjat "Jac" wrote: hi, I have 2 files, Files A (old) contained data in below order:- Brand Type Unit Flag AA 123 2 Yes AB 234 No No In File B, data are organized in below order:- Brand Type Unit Flag AA 123 2 AB 234 3 CC 135 5 What I would like to know is how to compare each value in Brand, Type & Unit whether they are the same in each file; if they are the same then the Flag value in File A will be returned; else No will be retruned in File B. Actually, I have tried out using Vlookup functions but unfortunately, this function can only compare data in 1 column. So, is there any other functions that can help in solving this problem? Thanking in advanced. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Multi-Columns of Data in Different Files
Hi, Farhad
I have got the answer already... In FileA, the extra column should be on the left of Flag column. Anyway, thanks for your guidance! "Jac" wrote: Hi, Farhad Thanks for your reply! The formula does work but the values in E column are being returned to Flag clolumn instead of the Flag values. Plus, by using FALSE as the range_lookup value, once the data does not matched; an error message will be returned instead of No values from Flag column in File A. is there any other functions can help to solve these? Thanks! "Farhad" wrote: in this case an extra column will help you to use vlookup as follow: file A A B C D E 1 Brand Type Unit Flag extra column 2 AA 123 2 Yes =A1&B1&C1 3 AB 234 No 4 No file B A B C D E Brand Type Unit Flag 1 AA 123 2 =vlookup(E1,fileA!E1:E?,1,false) =A1&B1&C1 2 AB 234 3 3 CC 135 5 Hope it works -- Farhad Hodjat "Jac" wrote: hi, I have 2 files, Files A (old) contained data in below order:- Brand Type Unit Flag AA 123 2 Yes AB 234 No No In File B, data are organized in below order:- Brand Type Unit Flag AA 123 2 AB 234 3 CC 135 5 What I would like to know is how to compare each value in Brand, Type & Unit whether they are the same in each file; if they are the same then the Flag value in File A will be returned; else No will be retruned in File B. Actually, I have tried out using Vlookup functions but unfortunately, this function can only compare data in 1 column. So, is there any other functions that can help in solving this problem? Thanking in advanced. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing columnar data to row data | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Data in table, may need to convert to columns with OFFSET? | Excel Discussion (Misc queries) | |||
Comparing Data in 2 columns | Excel Worksheet Functions | |||
Comparing data in two columns and highlighting the data | Excel Worksheet Functions |