在excel中查找两个数组(两个范围)之间的联合，并显示它们共同的值

Okay, so this one is a little beyond my scope and I have been unable to find any examples of this (I have searched Google, the Stack, MSDN and several forums).

好吧，这个有点超出我的范围，我找不到任何例子(我搜索了谷歌、栈、MSDN和几个论坛)。

Lets assume Array1 consists of values in range A1:A3 one Sheet16 (one column but three rows). This array contains values 1,2,3
Lets also assume Array2 consists of values in ranges A1:C1; values, 1, 2 and 3 (one row but 3 columns).

让我们假设Array1包含A1:A3一个Sheet16范围内的值(一列但三行)。这个数组包含值1,2,3，我们还假设Array2包含范围为A1:C1的值;值1、2和3(一行但3列)。

How would I, see picture below, find the union between the two arrays, and then hide all the columns on sheet 17 that aren't within the union of the two arrays (sets).

我如何找到这两个数组之间的联合，然后将不属于这两个数组(集合)的所有列隐藏在第17页上?

In other words, how would I hide column A and column C in sheet 17 (as range B1 contains the union between the two arrays, that being "2".

换句话说，如何在表17中隐藏列A和列C(因为范围B1包含两个数组之间的联合，即“2”)。

I know I can establish the two array as follows, I am just unsure where to go from there:

我知道我可以建立两个数组如下，我只是不确定从哪里开始:

Sub Array_123()

Dim myarray As Variant, myarray2 As Variant

myarray = Application.Transpose(ThisWorkbook.Worksheets("sheet16").Range("a1:a3").value)
myarray2 = Application.Transpose(ThisWorkbook.Worksheets("sheet17").Range("a1:c1").value)

End Sub

Please see below, any help is greatly appreciated!! Thanks!

请见下面，任何帮助都是非常感谢!!谢谢!

2 个解决方案

#1

Not the best I have written, but does what I understood you want

不是我写的最好的，但我理解你想要的

Explore the List of columns of Sheet17
查看Sheet17的列列表

When column header is found in list of values of Sheet16, keep visible otherwise hides (Rank is not longer relevant)

当在Sheet16的值列表中找到列标头时，保持可见，否则将隐藏(Rank不再相关)

Private Sub hideUnmatchCol()

    Dim col As Integer
    Dim rc As Variant

    col = 1

    Do Until Worksheets("Sheet17").Cells(1, col).Value = ""

       On Error Resume Next
       rc = ""
       rc = Worksheets("Sheet16").Columns("A:A").Find(What:=Worksheets("Sheet17").Cells(1, col).Value, _
            After:=Worksheets("Sheet16").Cells(1, 1),  LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Address
       If rc = "" Then
          Worksheets("Sheet17").Columns(col).EntireColumn.Hidden = True
       Else
          Worksheets("Sheet17").Columns(col).EntireColumn.Hidden = False
       End If
       On Error GoTo 0
       col = col + 1
    Loop

End Sub

Hope that helps

希望这有助于

#2

If i understand the problem correctly, you are trying to use sheet 16's values as an index for the columns on sheet 17. If the index matches, column.hidden==False, else column.hidden==True.

如果我理解正确的话，您正在尝试使用表16的值作为表17中的列的索引。如果索引匹配，列。隐藏的= = False,否则column.hidden = = True。

Now, this can be accomplished easily using the following approach.

现在，可以使用以下方法轻松完成此任务。

Sub test()

子测试()

Dim myarray As Variant, myarray2 As Variant

'sheet 2 is dictating what happens with the columns on sheet one

myarray = Worksheets("Sheet1").Range("a1:d1") '2 dimentional array
myarray2 = Worksheets("Sheet2").Range("a1:a4") '2 dimentional array

'check if the arrays are of the same length. We are comparing the rows of one to the columns of the other
If (UBound(myarray2, 1) = UBound(myarray, 2)) Then 'if row length of one is the same as column length of the other
    For arrayIndex = 1 To UBound(myarray2, 1) 'for loop based on the length of the elements in the array
        If (myarray(1, arrayIndex) = myarray2(arrayIndex, 1)) Then 'if the elements at each index point in the arrays have a matching value hide the column
            Worksheets("Sheet1").Columns(arrayIndex).Hidden = True
        End If
    Next arrayIndex
End If

End Sub

终止子

#1