如何检查两个列表进行匹配 [英] How to check two lists for match
问题描述
我想知道哪个是Excel中检查两个文本列表的最佳方式。我有一个列表,其中有文件名存在于我的硬盘上,我希望参考这些第二个列表,我应该收到的文件名,以确保他们没有丢失。
我试过
= VLOOKUP(A2,Sheet9!A:B,2,FALSE)
虽然这带来了一个值,但与列表中的值不符。
如果你需要更多的澄清,那么让我知道。
我目前无法发布图像,所以希望这个(例子)会清除一些东西。
应该发送的文件列表: -
- CC-2245-SXV.PDF
- CC-2222-SXV.PDF
- CC-0001-SXV.PDF
- DF-2322-CDSC.PDF
- CC-6221-SXV.PDF
我的硬盘上的文件名: -
-
- CC-0161-SXV.PDF
- CC-2221-CCXV.PDF
- CC-6221-SXV.PDF
- DF-2322-CDSC.PDF
我想要自动化的原因是因为我在处理1000的文件。
shA.t的答案可能会更容易长期,但如果你想与Vlookup一起去,你可以做这样的事情:
= IF(ISNA(VLOOKUP(HDD,Sent,1,FALSE)),CONCATENATE(File not sent: ,HDD),VLOOKUP(HDD,Sent,1,FALSE))
在这种情况下,我把在命名范围内的HDD上的文件列表,简称为HDD,以及名为发送的命名范围中发送的文件列表。您可以轻松地将名称范围替换为Sheet1!B1:B5,如果您想要的。
如果无法在发送列表中找到一个文件在HDD列表中,因此返回#N / A,而是返回一条消息,告诉您HDD列表中没有发送哪个文件。
如果您打算在单个工作表上只列出三列(硬盘列表,发送列表和公式),您可以删除其余的列表,只需使用: p>
= VLOOKUP(HDD,Sent,1,FALSE)
但是,如果您将两个列表分成不同的工作表或单独的Excel文件,则可以使用第一个公式,并且可以更容易地查看哪些文件丢失。
I want to know which would be the best way to check two lists of text within Excel. I have one list, that has file names which exist on my HDD and I wish to reference these against a second list, of file names which I should have received to make sure that none of them are missing.
I did try
=VLOOKUP(A2,Sheet9!A:B,2,FALSE)
Although this brings back a value it does not match the value in the list.
If you require more clarification then let me know. I can't post images at the moment so hopefully this (example) will clear things up a bit.
List of files supposedly sent:-
- CC-2245-SXV.PDF
- CC-2222-SXV.PDF
- CC-0001-SXV.PDF
- DF-2322-CDSC.PDF
- CC-6221-SXV.PDF
File names on my HDD:-
- CC-0001-SXV.PDF
- CC-0161-SXV.PDF
- CC-2221-CCXV.PDF
- CC-6221-SXV.PDF
- DF-2322-CDSC.PDF
The reason I want to automate this is because I'm dealing with 1000's of files.
shA.t's answer would likely be easier long term but if you want to go with a Vlookup you could do something like this:
=IF(ISNA(VLOOKUP(HDD,Sent,1,FALSE)),CONCATENATE("File not sent: ",HDD),VLOOKUP(HDD,Sent,1,FALSE))
In this case I put the list of files on the HDD in a named range simply named HDD and a list of sent files in a named range named Sent. You could just as easily replace the named ranges with something like Sheet1!B1:B5 if you wanted to.
If it fails to find a file in the Sent list that is in the HDD list thus returning #N/A instead it returns a message telling you which file from the HDD list wasn't sent.
If you plan on just having the three columns (HDD list, Sent list, and formula) on a single worksheet you could remove the rest and just use this:
=VLOOKUP(HDD,Sent,1,FALSE)
But the first formula will work if you have the two lists in separate worksheets or separate excel files and make it easier to see which files are missing.
这篇关于如何检查两个列表进行匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!