单个细胞中标准的多个结果

So I have been going at this for awhile now and I am either just missing something or just dumb. Essentially I am trying to do something similar to the countifs function in Excel where you can use multiple criteria but instead of returning a count I am looking to return the matched cell values in a single cell.

所以我现在已经有一段时间了,我或者只是缺少一些东西或者只是愚蠢。基本上我正在尝试做类似于Excel中的countifs功能,你可以使用多个条件但不是返回计数我想在单个单元格中返回匹配的单元格值。

I have named ranges for all three fields that is used to pull into the results for the numbers but now I am looking for a way to grab each case and notes and put them into the perspective cell as shown in the last image. I dont mind using VBA to achieve this or even formulas if possible I have just gone through everything and just cant figure this one out.

我已经为所有三个字段命名范围,这些字段用于输入数字的结果,但现在我正在寻找一种方法来抓取每个案例和注释并将它们放入透视单元格中,如上图所示。我不介意使用VBA来实现这个甚至是公式,如果可能的话我已经完成了所有事情并且无法想象这个。

This is essentially what I am trying to achieve.

这基本上就是我想要实现的目标。

2 个解决方案

#1

Try this code:

试试这段代码:

Sub Demo()
    Dim dict1 As Object, dictApp As Object, dictNotApp As Object
    Dim c1 As Variant, k As Variant, j As Variant
    Dim i As Long, lastRow As Long, rowCount As Long
    Dim rngName As Range, rngCase As Range, rngNotes As Range, rngFound As Range
    Dim FirstAddress As String, strApp As String, strNotApp As String, strNotes As String
    Dim dataSheet As Worksheet, outputSheet As Worksheet

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'set you worksheets here
    Set dataSheet = ThisWorkbook.Sheets("Sheet1")
    Set outputSheet = ThisWorkbook.Sheets("Sheet2")

    Set dict1 = CreateObject("Scripting.Dictionary")
    Set dictApp = CreateObject("Scripting.Dictionary")
    Set dictNotApp = CreateObject("Scripting.Dictionary")

    'get last row with data
    lastRow = dataSheet.Cells(Rows.Count, "A").End(xlUp).Row
    'you can replace following ranges to your named ranges
    Set rngName = dataSheet.Range("A2:A" & lastRow)
    Set rngCase = dataSheet.Range("B2:B" & lastRow)
    Set rngNotes = dataSheet.Range("C2:C" & lastRow)

    'put unique names to dict1
    c1 = dataSheet.Range("A2:A" & lastRow)
    For i = 1 To UBound(c1, 1)
        dict1(c1(i, 1)) = 1
    Next i

    rowCount = 2 'this is the starting row no for ouputSheet, row 1 being the header
    For Each k In dict1.keys
        strApp = ""
        strNotApp = ""
        strNotes = ""

        'for each unique name get the values of case and notes
        Set rngFound = dataSheet.Columns(1).Find(What:=k, LookAt:=xlWhole, MatchCase:=False)
        If Not rngFound Is Nothing Then
            FirstAddress = rngFound.Address
            Do
                If rngFound.Offset(0, 2) = "Approved" Then
                    'if value of notes is approved put data in dictApp
                    dictApp.Add rngFound.Offset(0, 1), rngFound.Offset(0, 2)
                Else
                    'if value of notes is not approved put data in dictNotApp
                    dictNotApp.Add rngFound.Offset(0, 1), rngFound.Offset(0, 2)
                End If

                Set rngFound = rngName.FindNext(rngFound)
            Loop While Not rngFound Is Nothing And rngFound.Address <> FirstAddress

            'create case string for approved notes
            For Each j In dictApp.keys
                If strApp = "" Then
                    strApp = j
                Else
                    strApp = strApp & vbCrLf & j
                End If
            Next
            'create case and notes string for notes not approved
            For Each j In dictNotApp.keys
                If strNotApp = "" Then
                    strNotApp = j
                    strNotes = dictNotApp(j)
                Else
                    strNotApp = strNotApp & vbCrLf & j
                    strNotes = strNotes & vbCrLf & dictNotApp(j)
                End If
            Next
        End If

        'display values in outputSheet
        outputSheet.Cells(rowCount, 1) = k
        outputSheet.Cells(rowCount, 2) = Application.WorksheetFunction.CountIf(rngName, k)
        outputSheet.Cells(rowCount, 3) = Application.WorksheetFunction.CountIfs(rngName, k, rngNotes, "<>Approved")
        outputSheet.Cells(rowCount, 4) = strApp
        outputSheet.Cells(rowCount, 5) = strNotApp
        outputSheet.Cells(rowCount, 6) = strNotes

        dictApp.RemoveAll
        dictNotApp.RemoveAll
        rowCount = rowCount + 1
    Next k
    'center align the data
    With outputSheet.UsedRange
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
End Sub

See image for reference:

见图片参考:

#2

It looks like you are just trying to summarize data. If so, a PivotTable will be your easiest and best option - plus there is no VBA code or formula writing! I added in one extra column for counting purposes and created two separate PivotTables. Below is an example of what I put together... hope this helps!

看起来您只是想总结数据。如果是这样,数据透视表将是您最简单和最好的选择 - 而且没有VBA代码或公式编写!我添加了一个额外的列用于计数目的,并创建了两个单独的数据透视表。下面是我放在一起的例子......希望这有帮助!

#1