删除重复项并显示最近的日期 [英] Remove duplicates and show recent dates

问题描述

我在A单元格(23，i)和A单元格(24，i)列中的excel工作表上有数据，如下所示喜欢

I have data on an excel worksheet in column A cells(23,i) and A celles(24,i) ordered like this d like this

我想在列表框中显示数据而不重复，并且只显示每个值的最新日期(N IT)，如下所示:

I want to show the data in a listbox without duplicates and showing only recent dates for each value (N IT) like this:

要显示该列表，我创建了一个按钮，并设置此代码:

To show the list I created a button, and set this code :

Private Sub CommandButton1_Click()
  If Me.ListBox_Pers.ListIndex = -1 Then
       MsgBox ("Vous n'avez pas selectionner une personne")
  Else

      Acrtu_IT
      Load UF_Profil_Edit1
      UF_Profil_Edit1.Show
     'UF_Choix_Pers_Edit.ListBox_Pers.Clear

  End If
End Sub

其中:私有Sub Actu_IT()

Personne = UF_Profil_Edit1.TextBox_Nom & " " & UF_Profil_Edit1.TextBox_Prenom.Value
Set ws = ActiveWorkbook.Worksheets(Personne)
UF_Profil_Edit1.ListBox_IT.Clear
Fin_Col_IT = ws.Cells(23, 256).End(xlToLeft).Column
UF_Profil_Edit1.ListBox_IT.ColumnCount = 4
UF_Profil_Edit1.ListBox_IT.ColumnWidths = "50;450;60;20"

Set Plage = ws.Rows(23)
Set Plage2 = ws_Liste_IT.Columns(2)
For i = 2 To Fin_Col_IT
   Val_Cherch = ws.Cells(23, i).Value
   Set Trouve = Plage.Cells.Find(what:=Val(Val_Cherch))
   If Trouve Is Nothing Then
   Else

      Set Trouve2 = Plage2.Cells.Find(what:=Val(Val_Cherch))
      If Trouve2 Is Nothing Then
      Else

         UF_Profil_Edit1.ListBox_IT.AddItem Trouve2.Offset(, 2)
         '---------------Nom de l'IT------------------------------
         UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 1) = Trouve2.Offset(, 1)
         '---------------Date de fin de la formation------------------------------
        UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 2) = ws.Cells(24, Trouve.Column)
 '---------------n° IT---------------------
        UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 3) = Trouve2
       '-----------°DE-------------------

     End If
  End If
Next i

'---sorting data in alphabetic order-----------------
    Dim a()
    a = UF_Profil_Edit1.ListBox_IT.List
If UBound(a, 1) > 1 Then
    Module2.Tri a(), LBound(a), UBound(a), 0
    UF_Profil_Edit1.ListBox_IT.List = a
Else
End If

如何仅显示单元格A(23，i)中每个值的最近日期，谢谢

How can show only recent dates for each value in celle A(23,i), Thank you

推荐答案

请使用下一个函数来处理您的列表框(据我了解)，您需要:

Please use the next function, to process your list box as (I understood) you need:

Private Function arrangeListBox(lst As MSForms.ListBox)
 Dim i As Long, refDate As Date, j As Long
Beginning:
 For i = 0 To lst.ListCount - 1
    refDate = DateValue(lst.list(i, 2))
    For j = 0 To lst.ListCount - 1
        If lst.list(i, 0) = lst.list(j, 0) And _
            DateValue(lst.list(j, 2)) < refDate Then
            lst.RemoveItem (j): GoTo Beginning
        End If
    Next j
 Next i
End Function

该函数必须在代码末尾以以下方式调用:

The function must be called at the end of your code as:

arrangeListBox UF_Profil_Edit1.ListBox_IT

请确认它是否满足您的要求.如果您只想在列表框中加载适当的行(遵守最近日期的规则)，则必须同时提供处理中涉及的两个工作表(以及相关数据，以使它们能够被理解)...

Please, confirm that it does what you need. If you would like to load the list box only with the appropriate lines (respecting the rule of most recent date) you must provide both sheets involved in processing (with relevant data, to make them able to be understood)...

这篇关于删除重复项并显示最近的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！