在多列中查找多个实例

in Excel I wish to sort and filter some entries from multiple columns in a single column. The table looks like this:

在Excel中，我希望对单个列中多列的某些条目进行排序和过滤。该表如下所示：

    Day    Product.ID Quantity Time Product.ID Quantity Time Product.ID Quantity Time  
    1/1/17 9987/7     2500     8    11774      500      12   447/77     1400     4 
    2/1/17 9987/7     2350     8    2248/5     600      11   9987/7     1400     4 
    3/1/17 2248/5     3500     7    11774      650      12   447/77     1400     4 
    6/1/17 447/77     5500     8    11774      550      10   447/77     1400     4 
    7/1/17 9987/7     2000     6    2248/5     500      12   11774      1400     4

I would like to generate a "query" where I can have a report in a similar fashion:

我想生成一个“查询”，我可以以类似的方式获得报告：

    Product.ID: 9987/7 (drop down menu)
    Day    Quantity Time
    1/1/17 2500     8
    2/1/17 2350     8
    2/1/17 1400     4
    7/1/17 2000     6

And from there I can extrapolate all my data and conclusions. I know how to do it checking a single column and INDEX or VLOOKUP the other cells but I can't figure out how to do it this way. I can't transpose the data on a single column since they come from an already compiled sheet with production data from each machine.

从那里我可以推断出我的所有数据和结论。我知道如何检查单个列和INDEX或VLOOKUP其他单元格，但我无法弄清楚如何这样做。我无法将数据转置到单个列上，因为它们来自已编译的工作表，其中包含来自每台计算机的生产数据。

Thank you all

谢谢你们

1 个解决方案

#1

I tried to recreate your spreadsheet And put the results in Cells L.

我尝试重新创建您的电子表格并将结果放入Cells L.

I wrote some quick VBA Code that might act as reference to solve your problem. You can make a button to trigger the filter function and let it populate.

我写了一些快速的VBA代码，可以作为解决问题的参考。您可以创建一个按钮来触发过滤功能并让它填充。

Sub filter():
Dim i, j, k As Integer
Dim product_id As String
Dim column_len, row_len As Integer

'clearing content from previous entry
Sheets("Sheet1").Range("L3:N6").ClearContents

'Determining variable for row and columns
product_id = Sheets("Sheet1").Range("M2").Value
column_len = Cells.CurrentRegion.Columns.Count
row_len = Cells.CurrentRegion.Rows.Count
k = 3

'for loop through the columns and row to find the product_id and populate 
'the results box with Date, Product.ID and Quantity

For i = 1 To column_len
    If (Sheets("Sheet1").Cells(1, i).Value = "Product.ID") Then
        For j = 1 To row_len
            If (Sheets("Sheet1").Cells(j, i).Value = product_id) Then
                Sheets("Sheet1").Cells(k, 12).Value = Sheets("Sheet1").Cells(j, 1).Value
                Sheets("Sheet1").Cells(k, 12 + 1).Value = Sheets("Sheet1").Cells(j, i).Value
                Sheets("Sheet1").Cells(k, 12 + 2).Value = Sheets("Sheet1").Cells(j, i + 1).Value
                k = k + 1
            End If
        Next j
    End If
Next i

End Sub

#1