vlookup - 是否可以使用 vlookup 来提取这样的特定信息？

Question

我试图从每组数字中提取以下内容 -

CHG000509443 05-16-2016 22:48:41 for the Requested
CHG000509443 05-17-2016 13:14:35 for the Approved

注 1 - 它始终是每个组中第一个请求 - 参见 CHG000509448

注 2 - 每个组中的批准持续时间始终为 0 - 参见 CHG000510593

Answer 1

VBA 中的以下代码可以完成此操作：

Public Sub ReqandAppr()
    '****************
    ' variables
    SourceSheet = "Sheet1" 'name of source sheet
    DestSheet = "Sheet2"   ' name of destination sheet
    columntocheck = 5  'Column to check "Requested" or "Approved"
    firstrow = 2 'first row in source sheet
    destrow = 1  'first row in destination sheet
    'end of variables
    '*******************
    Dim wkb As Workbook
    Dim wksSource, wksDest As Worksheet
    Set wkb = ThisWorkbook
    Set wkSource = wkb.Sheets(SourceSheet)
    Set wkDest = wkb.Sheets(DestSheet)
    wkDest.Rows.Clear
    totalrows = wkSource.Cells(Rows.Count, 1).End(xlUp).Row
    For i = firstrow To totalrows
        If wkSource.Cells(i, 1) <> wkSource.Cells(i - 1, 1) Then
            requestedfound = False
            approvedfound = False
        End If
        Select Case wkSource.Cells(i, columntocheck)
            Case "Requested"
                If requestedfound = False Then
                    wkSource.Rows(i).Copy Destination:=wkDest.range("A" & destrow)
                    requestedfound = True
                    destrow = destrow + 1
                End If
            Case "Approved"
                If approvedfound = False And wkSource.Cells(i, columntocheck - 1) = 0 Then
                    wkSource.Rows(i).Copy Destination:=wkDest.range("A" & destrow)
                    approvedfound = True
                    destrow = destrow + 1
                End If
        End Select
    Next i
End Sub

使用ALT+打开 VBA/MAcros F11，在本工作簿插入一个新的模块并将代码粘贴在右侧。

检查变量的值并执行。