Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula/a way to find unmatch items in two file
Hello
I have two files (name it 1 & 2) with a list of items. some of the items in file2 doesn't exist in file1. I want to find them as it is a long list manually it will take along time. I used match formula in a new sheet to prepare a report there is 2 problem: * I wrote in a cell: =MATCH('file1'!F4:F252,'[file2.xls]Hu'!$D$5:$D$306,0) and it gives me #NA. I can undrestand that such report can't be prepared in just a cell! so what should I do? * Even using MATCH formula will give me the answer of the oposit of my question. I want to find the items which doesn't exist in file1 but this formula (if worked!) would give me the place of items which exist! Please kindly help me to solve this problem. I am not proffessional in using formula to answer my question and sometimes I can't find my required formula in excel Help. Thanks in advance Bahareh |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula/a way to find unmatch items in two file
To make it less confusing, you could try it like this
With file2.xls simultaneously open, In your sheet: file1, Using say, col G Put in G4: = IF(F4="","",--ISERROR(MATCH(F4,[file2.xls]Hu!$D$5:$D$306,0))) Copy G4 down to G252 (the last row of data in col F). G4:G252 will flag matched items as 0's, unmatched items as 1's. You could then select G3:G252 and apply autofilter, filter in G3 for: 1, to retrieve all unmatched items -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bahareh" wrote: Hello I have two files (name it 1 & 2) with a list of items. some of the items in file2 doesn't exist in file1. I want to find them as it is a long list manually it will take along time. I used match formula in a new sheet to prepare a report there is 2 problem: * I wrote in a cell: =MATCH('file1'!F4:F252,'[file2.xls]Hu'!$D$5:$D$306,0) and it gives me #NA. I can undrestand that such report can't be prepared in just a cell! so what should I do? * Even using MATCH formula will give me the answer of the oposit of my question. I want to find the items which doesn't exist in file1 but this formula (if worked!) would give me the place of items which exist! Please kindly help me to solve this problem. I am not proffessional in using formula to answer my question and sometimes I can't find my required formula in excel Help. Thanks in advance Bahareh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find certain items in worksheet? | Excel Discussion (Misc queries) | |||
How do I find certain items in worksheet? | Excel Discussion (Misc queries) | |||
How do I find all items with the same value without using filters | Excel Worksheet Functions | |||
How can I find multiple items? | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) |