如果条目出现在另一列中，如何删除它们？ [英] How do I delete entries from one column if they appear in another?

问题描述

两列：A列包含可用期刊标题的完整列表； B列包含我的图书馆已预订的那些条目。

Two columns: Column A contains a complete list of available journal titles; Column B contains the ones to which my library already subscribes.

如果它们也出现在B列中，是否可以从A列中删除条目？

Is it possible to delete entries from Column A if they also appear in Column B?

换句话说，我想燕尾两列，以便两列都没有条目。

In other words, I want to 'dovetail' two columns so that no entry is represented in both columns.

推荐答案

您可以将ADO用于数据库类型的操作，例如：

You can use ADO for database-type actions, for example:

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim i As Integer

''http://support.microsoft.com/kb/246335

strFile = ActiveWorkbook.FullName

''Note HDR=No, so F1,F2 etc is used for column names
''If HDR=Yes, the names in the first row of the range
''can be used.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT F1 FROM [Sheet9$] WHERE F2 Is Null OR UCase(F2)<>UCase(F1)"

rs.Open strSQL, cn, 3, 3

Worksheets("Sheet10").Cells(2, 1).CopyFromRecordset rs

这篇关于如果条目出现在另一列中，如何删除它们？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！