Excel VBA 匹配和排列行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4379213/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA to match and line up rows
提问by babasimana
I have an Excel document with columns A to J. I have columns K to N with related data, but not aligned.
我有一个包含 A 到 J 列的 Excel 文档。我有包含相关数据的 K 到 N 列，但没有对齐。
I need to match value from value in column F with value in column K so they are lined up. When I shift K, I have to shift L, M, N together.
我需要将 F 列中的值与 K 列中的值进行匹配，以便将它们对齐。当我移动 K 时，我必须一起移动 L、M、N。
I cannot sort columns A to J - they must remain in place.
我无法对 A 列到 J 列进行排序 - 它们必须保持原位。
Example before:
之前的例子：
A B C D E F G H I J K L M N
data data data data data record1 data data data data record3 data data data
data data data data data record2 data data data data record1 data data data
data data data data data record3 data data data data
data data data data data record4 data data data data
Example after:
之后的示例：
A B C D E F G H I J K L M N
data data data data data record1 data data data data record1 data data data
data data data data data record2 data data data data
data data data data data record3 data data data data record3 data data data
data data data data data record4 data data data data
回答by Fionnuala
The easiest way would probably be ADO.
最简单的方法可能是 ADO。
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer
''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.
strFile = ActiveWorkbook.FullName
''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT * " _
& "FROM [Sheet2$A1:J5] a " _
& "LEFT JOIN [Sheet2$K1:N5] b " _
& "ON a.F=b.k "
rs.Open strSQL, cn, 3, 3
''Pick a suitable empty worksheet for the results
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
回答by ja72
Sort columns K - M and move them further away (like in X-Z)
对列 K - M 进行排序并将它们移得更远（如在 XZ 中）
In columns K-M add a VLOOKUP()function to pull data from X-Z based on column F.
在 KM 列中添加一个VLOOKUP()函数以根据 F 列从 XZ 中提取数据。
To make it pretty pull out the row# with the INDEX()function and only if found copy the data from X-Z to K-L with the MATCH()function. Otherwise return an empty string.
为了使它漂亮地用INDEX()函数拉出行#，并且只有在找到时才用函数将数据从 XZ 复制到 KL MATCH()。否则返回一个空字符串。
Columns A-J with data, with column F containing the lookup values
包含数据的 AJ 列，F 列包含查找值
Column X-Z with reference table with X containing the loopup match values
带有参考表的 XZ 列，其中 X 包含循环匹配值
Add a column N with =MATCH((Value in F),(XYZ Table),FALSE)this produces either a #N/Aor a row number
添加一个列 N，=MATCH((Value in F),(XYZ Table),FALSE)这会产生一个#N/A或一个行号
Column K with =IF( NOT( ISNA(Value in N) ), INDEX((X Table), (Value in N) ), "")
K列与 =IF( NOT( ISNA(Value in N) ), INDEX((X Table), (Value in N) ), "")
Column L with =IF( NOT( ISNA(Value in N) ), INDEX((Y Table), (Value in N) ), "")
L列与 =IF( NOT( ISNA(Value in N) ), INDEX((Y Table), (Value in N) ), "")
Column M with =IF( NOT( ISNA(Value in N) ), INDEX((Z Table), (Value in N) ), "")
M列与 =IF( NOT( ISNA(Value in N) ), INDEX((Z Table), (Value in N) ), "")
Unless you want to do it in VBA, this works.
除非您想在 VBA 中执行此操作，否则此方法有效。
