Excel – Horizontal/Column filtering in Excel? Should be simple

microsoft excelmicrosoft-excel-2010

In Excel it is straight forward to apply a filter on a column (mark the region of interest, klick on data>filter). This way you get the handy option to show the content corresponding to, e.g., a value by a "simple click".

For example, say a I have the weekdays repeating over and over in one column and various data in the other columns. By the filtering described above, I can, with a click, show rows corresponding to, say, mondays only.

If we call the filtering described above vertical, how do I accomplish horizontal filtering in the same simplistic manner?

It should be very simple, but I cannot find any way to do this. Annoying!

Best Answer

Another way would be to use Transpose function in an array form. Steps as follows:
1. Select your destination area first.
2. In the formula box type following: "=Transpose(source)"
3. Instead of hitting enter, you must hit ctrl+shift+enter.

What step 3 does is create an array type that links to your source data. This is similar to copy/paste link except it transposes the data, at which point you just filter as usual.

If done properly your code may look like this:
{=TRANSPOSE(A1:Z100)}
In every single destination cell.

Related Solutions

Excel – Filtering Data Excel Data from Survey to Obtain E-mails

You can use VBA to create individual files for each club. The procedure FilterData below creates a file for each club within the same directory as the file containing the VBA code, which also should contain the responses within a sheet called Responses.

Option Explicit

Sub FilterData()
    Dim Responses As Worksheet
    Dim Column As Long

    Set Responses = ThisWorkbook.Worksheets("Responses")
    Column = 2

    Do While Responses.Cells(1, Column).Value <> ""
        With Workbooks.Add(xlWBATWorksheet)
            With .Worksheets(1)
                Responses.Cells.Copy .Cells
                .Columns(Column).AutoFilter Field:=1, Criteria1:="<>1"
                .Rows(2).Resize(.Rows.Count - 1).Delete Shift:=xlUp
                .Columns(2).Resize(, .Columns.Count - 1).Delete Shift:=xlShiftToLeft
            End With

            .Close SaveChanges:=True, Filename:=ThisWorkbook.Path & "\" & Responses.Cells(1, Column).Value
        End With

        Column = Column + 1
    Loop
End Sub

Best Answer

Related Solutions

Excel – Filtering Data Excel Data from Survey to Obtain E-mails

Related Question