Using VBA or Macros to get multiple results

J

jarust91

New Member
Joined
Sep 21, 2017
Messages
8
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.

[TABLE="width: 289"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Status[/TD]
[TD]Last Modified[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Completed[/TD]
[TD="align: right"]16-Aug[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Scheduled[/TD]
[TD="align: right"]15-Aug[/TD]
[/TR]
[TR]
[TD]Jimmy Doe[/TD]
[TD]Exited[/TD]
[TD="align: right"]14-Aug[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Completed[/TD]
[TD="align: right"]10-Aug[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Reg. Completed[/TD]
[TD="align: right"]4-Aug[/TD]
[/TR]
[TR]
[TD]Jimmy Doe[/TD]
[TD]Registration[/TD]
[TD="align: right"]1-Aug[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Unreachable[/TD]
[TD="align: right"]31-Jul[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Completed[/TD]
[TD="align: right"]31-Jul[/TD]
[/TR]
[TR]
[TD]Jimmy Doe[/TD]
[TD]Scheduled[/TD]
[TD="align: right"]29-Jul[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Reg. Completed[/TD]
[TD="align: right"]25-Jul[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Abandoned[/TD]
[TD="align: right"]25-Jul[/TD]
[/TR]
[TR]
[TD]Jimmy Doe[/TD]
[TD]Exited[/TD]
[TD="align: right"]15-Jul[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Scheduled[/TD]
[TD="align: right"]15-Jul[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Completed[/TD]
[TD="align: right"]14-Jul[/TD]
[/TR]
[TR]
[TD]Jimmy Doe[/TD]
[TD]Completed[/TD]
[TD="align: right"]14-Jul[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Scheduled[/TD]
[TD="align: right"]13-Jul[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]Completed[/TD]
[TD="align: right"]13-Jul[/TD]
[/TR]
[TR]
[TD]Jimmy Doe[/TD]
[TD]Scheduled[/TD]
[TD="align: right"]10-Jul[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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-ignore:padding; 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>[TABLE="width: 348"]
<!--StartFragment--> <colgroup><col width="87" span="4" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87"]John Doe[/TD]
[TD="class: xl63, width: 87, align: right"]16-Aug[/TD]
[TD="class: xl63, width: 87, align: right"]10-Aug[/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD="class: xl63, align: right"]31-Jul[/TD]
[TD="class: xl63, align: right"]14-Jul[/TD]
[TD="class: xl63, align: right"]13-Jul[/TD]
[/TR]
[TR]
[TD]Jimmy Doe[/TD]
[TD="class: xl63, align: right"]14-Jul[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

So you would have the names, followed by all of the dates in corresponding columns.
 
Upvote 0
jarust91 said:
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-ignore:padding; 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>[TABLE="width: 348"]
<tbody>[TR]
[TD="width: 87"]John Doe[/TD]
[TD="class: xl63, width: 87, align: right"]16-Aug[/TD]
[TD="class: xl63, width: 87, align: right"]10-Aug[/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD="class: xl63, align: right"]31-Jul[/TD]
[TD="class: xl63, align: right"]14-Jul[/TD]
[TD="class: xl63, align: right"]13-Jul[/TD]
[/TR]
[TR]
[TD]Jimmy Doe[/TD]
[TD="class: xl63, align: right"]14-Jul[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

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).

Code: 
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
 
Upvote 0
@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
Code: 
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
 
Upvote 0
Here is another macro for you to consider...
Code: 
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
I have no experience with Mac, but if Pivot Tables are available try it.

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


[TABLE="class: grid"]
<tbody>[TR]
[TD]
Status​
[/TD]
[TD]
Completed​
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Name​
[/TD]
[TD]
Last Modified
​
[/TD]
[/TR]
[TR]
[TD]
Jane Doe​
[/TD]
[TD]
13/jul​
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]
14/jul​
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]
31/jul​
[/TD]
[/TR]
[TR]
[TD]
Jimmy Doe​
[/TD]
[TD]
14/jul​
[/TD]
[/TR]
[TR]
[TD]
John Doe​
[/TD]
[TD]
10/ago​
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]
16/ago​
[/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps

M.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Extract data (Filter) from a table using criteria from a diferent table
Replies
5
Views
484
duggie33
D
E
  • Question Question
Dynamic Task Schedule
Replies
4
Views
499
EvenS8245
E
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
IIII
  • Solved
VBA: Print to pdf with custom or dynamic print area
Replies
1
Views
497
MCLIFTO8
MCLIFTO8
B
  • Question Question
How to make this IF formula work?
Replies
1
Views
242
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top