我有两个表:表 1:唯一 ID 列表(列表大小为 3)和表 2:项目列表(列表大小为 3)。
我如何创建第三个列表,其中对于表 1 中的每一行,它都会添加表 2 中具有唯一 ID 的所有行。因此,使用上面的示例数字,我们最终应该得到 9 行,表 1 中的每个唯一 ID 对应 3 行。
希望这有意义,我也希望下面的内容可读!
表格1:
UID Header
UID1
UID2
UID3
表2:
Header1 Header2
Name1 Value1
Name2 Value2
Name3 Value3
预期结果:
UIDH Header1 Header2
UID1 Name1 Value1
UID1 Name2 Value2
UID1 Name3 Value3
UID2 Name1 Value1
UID2 Name2 Value2
UID2 Name3 Value3
UID3 Name1 Value1
UID3 Name2 Value2
UID3 Name3 Value3
答案1
这正是你所要求的
Option Explicit
Sub OoohEckPirates()
Dim table1Start As Integer
table1Start = 2 ' UPDATE ME
Dim table2Start As Integer
table2Start = 7 ' UPDATE ME
Dim resultsTableStart As Integer
resultsTableStart = 12 ' UPDATE ME
'Create the header
Range("A11").Value = "UID Header" ' UPDATE ME
Range("B11").Value = "Name Header" ' UPDATE ME
Range("C11").Value = "Value Header" ' UPDATE ME
Dim header1Row As Integer
Dim header2Row As Integer
Dim resultsRow As Integer
Dim col As Integer
col = 65 'UPDATE ME - 65 = A, 66 = B, 67 = C. So, if your first column is A, set it to 65
Dim currentUid As String
header1Row = table1Start
resultsRow = resultsTableStart
Do While (Range("A" & header1Row).Value <> "")
currentUid = Range("A" & header1Row).Value
header2Row = table2Start
Do While (Range(Chr(col) & header2Row).Value <> "")
Range(Chr(col) & resultsRow).Value = currentUid
Do While (Range(Chr(col) & header2Row).Value <> "")
Range(Chr(col + 1) & resultsRow).Value = Range(Chr(col) & header2Row).Value
col = col + 1
Loop
col = 65
header2Row = header2Row + 1
resultsRow = resultsRow + 1
Loop
header1Row = header1Row + 1
Loop
End Sub
在运行之前,请先备份,以确保万无一失
前
后