Reputation: 102

Excel Filtering through list via Formula Logic

I would like to scroll through my list of thousands of email logs and find all people with the name of "Yoda" and get the ID number where Yoda is located.

I've tried formulas for vlookups and index matching but it doesn't work because I get repeated IDs. How can I filter my list as a formula so that I can just get all IDs for Yoda? I will be placing the formula for the IDs in another worksheet.

Email Logs:

Character       ID

[email protected]  789
[email protected]  664
[email protected]  113
[email protected] 115
[email protected] 998
[email protected]  446
[email protected] 500

What I would like to see in my new worksheet is:

Yoda    Luke   Anakin   Jabba

789     113    115      998
664     500
446

Upvotes: 0

Answers (3)

Computer_Nerd

Reputation: 102

I was able to figure out my problem! I couldn't record a macro to filter my list using the "Search Filter" for Yoda (and many other names) since there were too many Yoda variations (see Error Image). Instead I recorded a macro that uses the "Text Filters" and I filtered to text that contains "Yoda". I did this for the rest of my characters and I was able to automate my filtered results onto a new worksheet.

Thank you all for your contributions!

Upvotes: 0

Chronocidal

Reputation: 7951

Use the AGGREGATE function? You'll want Small (15) to do this in ascending order

=IFERROR(INDEX($B$1:$B$8,AGGREGATE(15, 6, ROW($A$1:$A$8)-1+POWER(10,9*--(LEFT($A$1:$A$8,LEN("Yoda"))<>"Yoda")), ROW()-1),1),"")

The -1+POWER(10,9*--(LEFT($A$1:$A$8,LEN("Yoda"))<>"Yoda")) bit means that any non-Yoda values throw an error in INDEX, resulting in =""

Upvotes: 0

Ron Rosenfeld

Reputation: 60174

You can do this fairly simply with Power Query aka Get & Transform which has been available in Excel since 2010. For earlier versions, VBA would be a good alternative.

All of the operations, except for creating the two custom columns, can be done from the User Interface. The custom columns require entering formulas into the create custom column dialog.

In Power Query:

Add a custom column to remove the @domain portion of the email. Name the column CharName

Text.Start([Character],Text.PositionOf([Character],"@"))

Remove the original Characters column
Group by CharName

Add a custom column that converts the resultant Table to a List

Table.Column([Count],"ID")

In the resultant IDs column, select the double arrow at the upper right of the column, and select to Extract Values with a comma delimiter
Split that column by delimiter (the comma) and new columns will be created
Remove the original Table column
Transpose the entire table
Promote the contents of the first row to be Headers

Here is the M-Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Character", type text}, {"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CharName", each Text.Start([Character],Text.PositionOf([Character],"@"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Character"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"CharName"}, {{"Count", each _, type table [ID=number, CharName=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "IDs", each Table.Column([Count],"ID")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"IDs", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "IDs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"IDs.1", "IDs.2", "IDs.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"IDs.1", Int64.Type}, {"IDs.2", Int64.Type}, {"IDs.3", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Count"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Yoda", Int64.Type}, {"Luke", Int64.Type}, {"Anakin", Int64.Type}, {"Jabba", Int64.Type}})
in
    #"Changed Type2"

And here is the final result, from your original data:

If you prefer a VBA method, you can create a dictionary of collections of ID's for each character. You'll need to work your way through the code below to understand it, and adapt it to your particular workbook & worksheet setup.

Option Explicit
'Set reference to Microsoft Scripting Runtime
Sub charIDs()
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim vSrc As Variant, vRes As Variant
    Dim D As Dictionary, COL As Collection, sKey As String
    Dim I As Long, J As Long
    Dim V As Variant

Set wsSrc = Worksheets("Source")
Set wsRes = Worksheets("Results")
    Set rRes = wsRes.Cells(1, 1)

With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 2).End(xlUp))
End With

Set D = New Dictionary
    D.CompareMode = TextCompare

'Create a dictionary of collections of Id's for each character
For I = 2 To UBound(vSrc, 1)
    sKey = Split(vSrc(I, 1), "@")(0)
    If Not D.Exists(sKey) Then
        Set COL = New Collection
        COL.Add vSrc(I, 2)
        D.Add Key:=sKey, Item:=COL
    Else
        D(sKey).Add vSrc(I, 2)
    End If
Next I

'create results array
I = 0
For Each V In D.Keys
    I = IIf(I > D(V).Count, I, D(V).Count)
Next V

ReDim vRes(0 To I, 1 To D.Count)

'Populate
J = 0
For Each V In D.Keys
    J = J + 1
    vRes(0, J) = V
    For I = 1 To D(V).Count
        vRes(I, J) = D(V)(I)
    Next I
Next V

'size and fill results range
Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    .EntireColumn.AutoFit
End With

End Sub

Upvotes: 1

Excel Filtering through list via Formula Logic

Answers (3)

Related Questions