如果在列中找到重复的单元格值，则返回值 [英] IF duplicate cell value found in column then return value

问题描述

我需要跟踪数据表中的一个人来确定该人移动到哪个位置的位置。

I need to track a person in a data sheet to determine from which location to which location the person moved.

如果一个人在第J列中出现一次，这意味着该人已经更改了位置，并且位置值位于列L中。为此，我有以下代码：

If a person appears more then one time in Column J that means the person has changed the location and the location value is in Column L. For this I have the following code:

=IF(J18=J19;IF(COUNTIF(J:J;J18)>1; "From "&L18 &" to "& IF(J18=J19;L19;"");"");"")

问题是如果人员更改位置超过两次。在列O到列AA中，我有一年的几个月，决定该人的位置。

The problem is if the person changes the location more than two times. In Column O to Column AA I have the months of the year which determines the location of the person.

如何修改此代码以执行上述操作：

How can I modify this code to do the above:

=IF(J18=J19;IF(COUNTIF(J:J;J18)>1; "From "&L18 &" to "& IF(J18=J19;L19;"");"");"")

推荐答案

这是一个用户定义函数（也称为UDF）来完成任务。

Here is a User Defined Function (aka UDF) to accomplish the task.

Function my_Travels(nm As Range, loc As Range, cal As Range)
    Dim n As Long, cnt As Long, v As Long, vLOCs As Variant, vTMPs As Variant
    Dim iLOC As Long, sTMP As String

    my_Travels = vbNullString   '"no travels"
    cnt = Application.CountIf(nm.EntireColumn, nm(1))

    If Application.CountIf(nm, nm(1)) = cnt And cnt > 1 Then
        Set loc = loc.Rows(1).Resize(nm.Rows.Count, loc.Columns.Count)
        Set cal = cal.Rows(1).Resize(nm.Rows.Count, cal.Columns.Count)

        'seed the array
        ReDim vLOCs(1 To cnt, 1 To cnt)
        For v = LBound(vLOCs, 1) To UBound(vLOCs, 1)
            vLOCs(v, 1) = cal.Columns.Count + 1
            vLOCs(v, 2) = cal.Columns.Count + 1
        Next v

        'collect the values into the array
        For n = 1 To nm.Rows.Count
            If nm.Cells(n, 1).Value2 = nm.Cells(1, 1).Value2 Then
                iLOC = Application.Match(1, Application.Index(cal, n, 0), 0)
                For v = LBound(vLOCs, 1) To UBound(vLOCs, 1)
                    If vLOCs(v, 1) = cal.Columns.Count + 1 Then
                        vLOCs(v, 1) = iLOC
                        vLOCs(v, 2) = n
                        Exit For
                    End If
                Next v
            End If
        Next n

        'sort the values in the array
        For v = LBound(vLOCs, 1) To (UBound(vLOCs, 1) - 1)
            For n = (v + 1) To UBound(vLOCs, 1)
                If vLOCs(v, 1) > vLOCs(n, 1) Then
                    vTMPs = Array(vLOCs(v, 1), vLOCs(v, 2))
                    vLOCs(v, 1) = vLOCs(n, 1)
                    vLOCs(v, 2) = vLOCs(n, 2)
                    vLOCs(n, 1) = vTMPs(0)
                    vLOCs(n, 2) = vTMPs(1)
                    Exit For
                End If
            Next n
        Next v

        'concatenate the locations from the array
        For v = LBound(vLOCs) To (UBound(vLOCs) - 1)
            sTMP = sTMP & "From " & loc.Cells(vLOCs(v, 2), 1) & " to " & loc.Cells(vLOCs(v + 1, 2), 1) & "; "
        Next v

        'truncate the string and return it
        sTMP = Left(sTMP, Len(sTMP) - 2)
        my_Travels = sTMP

    End If

End Function

>位置和日历单元格只需要由第一行定义。每个都有其高度（即行）重新定义，以保持与名称列表的一致性。

The Locations and the Calendar cells only need to be defined by the first row. Each has its height (i.e. rows) redefined to maintain consistency with the list of names.

在AB2（如上）中，公式是

In AB2 (as above) the formula is,

=my_Travels(J2:J$8, L2, O2:AA2)

根据需要填写。

这篇关于如果在列中找到重复的单元格值，则返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！