Filters or Arrays used to condense data to a smaller selection

slick225 · Sep 21st 2015

I want to be able to Type in or select the Activity Code from a dropdown and be presented with a listing of the Wk Days that the activity occur and a listing of the activity/activity name using either arrays, filters, or some other formula that will accomplish the goal.
Pivot tables work but the layout is not how I'd like to present the data.

In my "Target Results" example (provided in the attachment), BC in cell G6 is what I'd type in or select from dropdown and the result would be a filtered listed of A2:C26, the end results would show only the week days and Activity (name) from the list based on the Activity code entered in G6.

NOTE: If the data (A2:C26) has to be made into a table or if it or the formula has to be moved to another sheet I'm ok with that

My primary data is a listing of application screens that impact other app screens, and some screens are impacted my multiple screens, my goal is to create a spreadsheet that will allow me to select a screen and see what screens are impacted.

See Sample Spreadsheet attached.

Thank you,

jindon · Sep 22nd 2015

Re: Filters or Arrays used to condense data to a smaller selection

1) To Sheet1 code module

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "G6" Then Exit Sub
    Application.EnableEvents = False
    test
    Application.EnableEvents = True
End Sub

2) A Standard code module

Code

Sub test()
    Dim myCode, x
    Range("f8").CurrentRegion.Clear
    myCode = Range("g6").Value
    If IsEmpty(myCode) Then Exit Sub
    If Not IsNumeric(myCode) Then myCode = Chr(34) & myCode & Chr(34)
    With [a2].CurrentRegion
        x = Filter(Evaluate("transpose(if(" & .Columns(1).Address & "=" & _
        myCode & ",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
        If UBound(x) > -1 Then
            x = Split("1," & Join(x, ","), ",")
            x = Application.Index(.Value, Application.Transpose(x), [{3,2}])
            With Range("f8").Resize(UBound(x), 2)
                .Value = x
                .Borders.Weight = 2
            End With
        End If
    End With
End Sub

Display More

slick225 · Sep 24th 2015

Re: Filters or Arrays used to condense data to a smaller selection

Jindon, thank you for the VB Code, it works perfectly. I'm new to VB so it's also a great learning opportunity for me as well.
I do have a couple of additional questions. In my actual data I have the data elements (the data table similar the the table that started in A2 in the original sample) located in one sheet (Sheet2 (My Data)) while the query and results (originally in the sample as Cell G6 and F8) should appear on a separate sheet (Sheet 1 (My Query)). Where in the code can I designate VB to pull the data from a different worksheet? I'm attaching a better representation of a sample.

Also are there any methods available from a formula perspective (INDEX, ARRAY, etc.) that could also accomplish this same task, VBA looks cleaner and I like the results, but I'd like to see if a Formula method is available. I'm leaning heavily to the VB method.

See attached file

jindon · Sep 24th 2015

Re: Filters or Arrays used to condense data to a smaller selection

1) Worksheet module

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C5" Then Exit Sub
    Application.EnableEvents = False
    test
    Application.EnableEvents = True
End Sub

2) a Standard module

Code

Sub test()
    Dim myCode, x
    Range("g5").CurrentRegion.Offset(2).Clear
    myCode = Range("c5").Value
    If IsEmpty(myCode) Then Exit Sub
    If Not IsNumeric(myCode) Then myCode = Chr(34) & myCode & Chr(34)
    With Sheets("my data").[a2].CurrentRegion
        x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & "=" & _
        myCode & ",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
        If UBound(x) > -1 Then
            x = Split("1," & Join(x, ","), ",")
            x = Application.Index(.Value, Application.Transpose(x), [{3,2}])
            With Range("g5").Resize(UBound(x), 2)
                .Value = x
                .Borders.Weight = 2
            End With
        End If
    End With
End Sub

Display More

3) No idea about formula solution.

slick225 · Sep 24th 2015

Re: Filters or Arrays used to condense data to a smaller selection

Jindon, that worked perfectly.
Final Question, and I do really appreciate your help and knowledge.

Question: If I wanted to included a tally of the results how would you incorporate that into the code?
Example:
Cell B7 would be the lable cell (Number of Week Days), Cell C7 would give a count of everything returned for an entered Activity Code.

Tom.Jones · Sep 25th 2015

Re: Filters or Arrays used to condense data to a smaller selection

Hi,

Quote

Also are there any methods available from a formula perspective (INDEX, ARRAY, etc.) that could also accomplish this same task, VBA looks cleaner and I like the results, but I'd like to see if a Formula method is available. I'm leaning heavily to the VB method.

Both formula, are array formula and you have to use Control+Shift+Enter
G6 =IFERROR(INDEX(Table1[Wk Day],SMALL(IF(Table1[Activity Code]=$C$5,ROW(Table1[Activity Code])-2),ROWS(G$5:G5))),"")
H6 =IFERROR(INDEX(Table1[Activity],SMALL(IF(Table1[Activity Code]=$C$5,ROW(Table1[Activity Code])-2),ROWS(G$5:G5))),"")

slick225 · Sep 28th 2015

Re: Filters or Arrays used to condense data to a smaller selection

Thank you Tom, this is the type of formula I used over a coouple years ago. "If you don't use it, well...you know".

Thanks again for all the help

Filters or Arrays used to condense data to a smaller selection

Participate now!

Share