Using VBA or Macros to get multiple results

[jarust91]

Hello,

I currently have a table with a couple thousand pieces of data. What I am trying to do is create a formula of some type to know all the corresponding dates to a certain person based on their status.

Name	Status	Last Modified
John Doe	Completed	16-Aug
Jane Doe	Scheduled	15-Aug
Jimmy Doe	Exited	14-Aug
John Doe	Completed	10-Aug
Jane Doe	Reg. Completed	4-Aug
Jimmy Doe	Registration	1-Aug
John Doe	Unreachable	31-Jul
Jane Doe	Completed	31-Jul
Jimmy Doe	Scheduled	29-Jul
John Doe	Reg. Completed	25-Jul
Jane Doe	Abandoned	25-Jul
Jimmy Doe	Exited	15-Jul
John Doe	Scheduled	15-Jul
Jane Doe	Completed	14-Jul
Jimmy Doe	Completed	14-Jul
John Doe	Scheduled	13-Jul
Jane Doe	Completed	13-Jul
Jimmy Doe	Scheduled	10-Jul

<colgroup><col><col><col></colgroup><tbody>
</tbody>

This is what the table looks like. I would want to know all the corresponding dates from John Doe when his status is completed. Ideally, I would be able to do this for all of the names.

Any help would be great appreciated.

Thanks,

Jarust91

 

[Leith Ross]

Hello jarust91,

What would the output look like?

 

[jarust91]

Hello,

I would hope that the out put would look something like this:

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {mso-number-format:"d\\-mmm";}--></style>

John Doe	16-Aug	10-Aug
Jane Doe	31-Jul	14-Jul	13-Jul
Jimmy Doe	14-Jul

<!--StartFragment--> <colgroup><col width="87" span="4" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

So you would have the names, followed by all of the dates in corresponding columns.

 

[C with no eyes]

Hello,

I would hope that the out put would look something like this:

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {mso-number-format:"d\\-mmm";}--></style>

John Doe	16-Aug	10-Aug
Jane Doe	31-Jul	14-Jul	13-Jul
Jimmy Doe	14-Jul

<tbody>
</tbody>

So you would have the names, followed by all of the dates in corresponding columns.

I have not found an easy way to get it into the format you have shown above. However, assuming 'name', 'status' & 'last modified' are in columns A, B & C respectively, the following macro will provide you with a list of completed status in columns E & F sorted by name then date (newest to oldest).

Sub Completed()
'
' Completed Macro
'


'
    Columns("E:F").Select
    Selection.ClearContents
    
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("A:C").AutoFilter Field:=2, Criteria1:="Completed"
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
        ("C:C"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Copy
    Range("E1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Copy
    Range("F1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Range("A:C").AutoFilter Field:=2
    Application.CutCopyMode = False
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
        ("C:C"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Range("E1").Select
End Sub

 

[Fluff]

@jarust91
Hi & welcome to the board.
Whilst you have asked for a formula.
Here is a macro that should do what you want, if you're interested.
This is based on your data being in cols A to C, with a header in row 1

Sub Fltr_Dict()


    Dim Cl As Range
    Dim Cnt As Long
    
    Columns(2).AutoFilter field:=1, Criteria1:="Completed"
    With CreateObject("scripting.dictionary")
        For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
            If Not .exists(Cl.Value) Then
                .Add Cl.Value, Cl.Offset(, 2).Value
            Else
                .Item(Cl.Value) = .Item(Cl.Value) & "|" & Cl.Offset(, 2).Value
            End If
        Next Cl
        
        Columns(2).AutoFilter
        
        Range("E2").Resize(.Count).Value = Application.Transpose(.Keys)
        For Cnt = 0 To .Count - 1
            Range("F" & Cnt + 2).Resize(, UBound(Split(.Item(Range("E" & Cnt + 2).Value), "|")) + 1).Value _
                = Split(.Item(Range("E" & Cnt + 2).Value), "|")
        Next Cnt
    End With

End Sub

 

[Rick Rothstein]

Here is another macro for you to consider...

Sub Fltr_Dict()
  Dim R As Long, Status As String, Data As Variant
  Status = "Completed"
  Data = Range("A2", Cells(Rows.Count, "C").End(xlUp))
  With CreateObject("scripting.dictionary")
    For R = 1 To UBound(Data)
      If Data(R, 2) = Status Then .Item(Data(R, 1)) = .Item(Data(R, 1)) & Data(R, 3) & "|"
    Next
    Range("E2").Resize(.Count).Value = Application.Transpose(.Keys)
    Range("F2").Resize(.Count).Value = Application.Transpose(.Items)
  End With
  Columns("F").TextToColumns , xlDelimited, , , False, False, False, False, True, "|"
End Sub

 

[jarust91]

Thanks Fluff,

I tried running this macro, but got an error. It says: "Run-time error '429': ActiveX component can't create object". When I click debug it highlights this row: "With CreateObject("scripting.dictionary")".

I apologize, but I probably should have specified that I am running this on a MacBook using the latest version of Excel for Mac.

 

[Fluff]

As far as I know, the scripting.dictionary won't work on a Mac.
If no-one else comes up with a solution I'll have another look tomorrow

 

[Marcelo Branco]

I have no experience with Mac, but if Pivot Tables are available try it.

Status --> Report Filter
Name and LastModified --> Row labels

Status​	Completed​
Name​	Last Modified ​
Jane Doe​	13/jul​
	14/jul​
	31/jul​
Jimmy Doe​	14/jul​
John Doe​	10/ago​
	16/ago​

<tbody>
</tbody>

Hope this helps

M.

 

[jarust91]

Thank you for this macro. It did the job for me. There is just one thing I was hoping to add to it. Is there a way so that it can also sort the "last modified" so that the oldest date for a certain person appears first.

Thanks so much.

Jarust91