Using "or" when filtering multiple columns in excel 2010....?

Question

I would like to filter data in the spreadsheet so that it shows all rows where column c or d =yes. If I filter column c then d, I only receive results where both columns = yes.
Please help!

Answer 1

You need to use ADVANCED FILTER feature.

The advanced filter uses user-defined criteria, that can be applied to several columns of data simultaneously. These criteria are specified on the same worksheet, rather than in the filter command box. To use the advanced filter you need to specify a data_range and a criteria_range.



STEP 1: Set up the DATA RANGE

• For this to work the data must have headings.

Let assume that row 1 is your headings, and the values are Data-A Data-B Data-C Data-D Data-E Data-F for column A,B,C,D,E,F.

Your data range is A1:F100 (including the headings)



STEP 2: Set up the CRITERIA RANGE or the filter rules

• Copy the headings to another part of the worksheet (the headings must exactly match the headings of your data)

Example: H1 will have the value Data-A, I1 Data-B, J1 Data-C, K1 Data-D, L1 DATA-E and M1 DATA F

HINT: You can copy only the headings from the columns you are going to filter instead of copying all the headings

• Now write the values you want to use to filter, taking in account that criteria listed on the same row are linked by the "AND" operator, and criteria listed on different rows are linked by the "OR" operator.

- On cell J2 write the criteria ="=yes"

(the value is on the column J with the DATA-C heading, then this criteria will be applied to column C that has the same heading)

- On row K3 write the criteria ="=yes" (the value is on column K with the the DATA-D heading, then this criteria will be applied to column D that has the same heading)

You have written your filter criteria, now lets use the ADVANCED FILTER feature:



STEP 3: Set up the filter

• From the ribbon select the DATA tab

• In the SORT and FILTER group select ADVANCED

• Under ACTION select where you want to filter your list

• On the LIST RANGE box, verify that the complete range of the list is included, for the example A1:F100

• On the CRITERIA RANGE box, select the criteria range that you wrote previouly, for the example, the range will be H1:M3

• Change any other option and click OK

Your list is filter

HINT: If you use the option to filter the list in place, then to display all the list clear the advanced filter



More on advanced filter: http://www.contextures.com/xladvfilter01.html



HINT: The operators to use are

< less than

<= less than or equal to

>= greater than or equal to

<> not equal to

="=text" Select cells whose contents are exactly equal to the string "text"

text Select cells whose contents begin with the string "text"

*text* Select cells whose contents contain the string "text"

Answer 2

Excel Filter Multiple Columns

Answer 3

This Site Might Help You.



RE:

Using "or" when filtering multiple columns in excel 2010....?

I would like to filter data in the spreadsheet so that it shows all rows where column c or d =yes. If I filter column c then d, I only receive results where both columns = yes.

Please help!

Answer 4

Edit: For the first part of your response, change this line in the macro (in both places in the macro):



For i = 1 To LastRow



To:



For i = 7 To LastRow



For the second part of your response, yes you can change the "D" to a "G" and the macro will evaluate those two rows.



===========





You can use a 'custom filter' macro like the following.



Copy the following macro to the clipboard:



Sub Custom_Filter()

Dim i, LastRow

LastRow = Application.Cells. SpecialCells(xlCellTypeLastCell).Row

For i = 1 To LastRow

If Cells(i, "A").EntireRow.Hidden = True Then

Cells.EntireRow.Hidden = False

Exit Sub

End If

Next

For i = 1 To LastRow

If UCase(Cells(i, "C").Value) = "YES" Or _

UCase(Cells(i, "D").Value) = "YES" Then

Cells(i, "C").EntireRow.Hidden = False

Else

Cells(i, "C").EntireRow.Hidden = True

End If

Next

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



Press ALT + F8



When the Macros window opens, highlight the macro and click 'Options...'



Enter a letter to be used as a keyboard shortcut and click 'OK'.



Close the Macros window.



To filter the data, press CTRL + your shortcut letter. To 'unfilter' the data, press CTRL + your shortcut letter again. This functions as a 'toggle' macro.

Answer 5

Select each complete address (one at a time) as it is above and copy then put the cursor in a column to the right and Paste, Transpose. This will place all the elements of the address in a column of its own.