Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I filter multiple columns at once?
In my spreadsheet I have the courses that each uni student is taking for the
current year. Each student (by row) can take up to 8 courses a year so there are 8 different columns. At certain points throughout the year, I will need to draw up a classlist for example of everyone in the 2nd year who is taking modernism. Therefore, I will need to filter all 8 columns at once to provide all the rows containg 'modernism'. However, because Excel provides additive filtering, once I have filtered column 1 for 'modernism', I have already probably hidden info I need from the other 7 columns and the next column I filter will only filter from the previous filter put in place. So, I would like to know how to filter all 8 columns for a particular course simultaenously because filtering 1 column at a time will only be additive and will hide info I need from the other columns. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I filter multiple columns at once?
Could you not have a column for each course by name? This way (for example)
column E is headed 'Modernism', and the entries in the rows below contain a flag - 'Y' might do - though you may have to develop a coding system according to your requirements. -- HTH Roger Shaftesbury (UK) "Aaron" wrote in message ... In my spreadsheet I have the courses that each uni student is taking for the current year. Each student (by row) can take up to 8 courses a year so there are 8 different columns. At certain points throughout the year, I will need to draw up a classlist for example of everyone in the 2nd year who is taking modernism. Therefore, I will need to filter all 8 columns at once to provide all the rows containg 'modernism'. However, because Excel provides additive filtering, once I have filtered column 1 for 'modernism', I have already probably hidden info I need from the other 7 columns and the next column I filter will only filter from the previous filter put in place. So, I would like to know how to filter all 8 columns for a particular course simultaenously because filtering 1 column at a time will only be additive and will hide info I need from the other columns. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I filter multiple columns at once?
"Aaron" skrev i en meddelelse ... In my spreadsheet I have the courses that each uni student is taking for the current year. Each student (by row) can take up to 8 courses a year so there are 8 different columns. At certain points throughout the year, I will need to draw up a classlist for example of everyone in the 2nd year who is taking modernism. Therefore, I will need to filter all 8 columns at once to provide all the rows containg 'modernism'. However, because Excel provides additive filtering, once I have filtered column 1 for 'modernism', I have already probably hidden info I need from the other 7 columns and the next column I filter will only filter from the previous filter put in place. So, I would like to know how to filter all 8 columns for a particular course simultaenously because filtering 1 column at a time will only be additive and will hide info I need from the other columns. You might want to try the following: If you have "courses" in for example B2:I100 try entering in cell J2 the array formula: =OR(B2:I2="modernism"). (Remember to hold down the Ctrl and Shift keys while pressing Enter. If entered correct the formula should look like this: {=OR(B2:I2="modernism")}. Copy the formula down through J100. You should get TRUE if there is at least one "modernism", otherwise FALSE. Now you can filter on column J. Regards Hans Knudsen |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I filter multiple columns at once?
"Roger Whitehead" wrote: Could you not have a column for each course by name? This way (for example) column E is headed 'Modernism', and the entries in the rows below contain a flag - 'Y' might do - though you may have to develop a coding system according to your requirements. I couldn't really do that because there are around 50-60 different courses to choose from and we'd rather not have 50-60 different columns. The plan is to have a drop down list in each cell of these 8 columns of all the courses available so I can then enter the correct one. This is why, courses will not necessarily be attributed to only 1 specific column. Cheers |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I filter multiple columns at once?
Hi,
Filter by using advanced criteria The Advanced Filter command on the Data menu lets you use complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount 30000.) to filter a range, but it works differently from the AutoFilter command in several important ways. It displays the Advanced Filter dialog box instead of the Custom AutoFilter dialog box. You do not type the complex criteria in the Advanced Filter dialog box as you do in the Custom AutoFilter dialog box. Rather, you type the complex criteria in a criteria range on the worksheet and above the range you want to filter. Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the complex criteria. Although you can filter a range in place, like the AutoFilter command, the Advanced Filter command does not display drop-down lists for the columns. 1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure there is at least one blank row between the criteria values and the range. 2. In the rows below the column labels, type the criteria you want to match. 3. Click a cell in the range. 4. On the Data menu, point to Filter, and then click Advanced Filter. 5. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows. 6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog . 7. To change how the data is filtered, change the values in the criteria range and filter the data again. Challa Prabhu "Aaron" wrote: In my spreadsheet I have the courses that each uni student is taking for the current year. Each student (by row) can take up to 8 courses a year so there are 8 different columns. At certain points throughout the year, I will need to draw up a classlist for example of everyone in the 2nd year who is taking modernism. Therefore, I will need to filter all 8 columns at once to provide all the rows containg 'modernism'. However, because Excel provides additive filtering, once I have filtered column 1 for 'modernism', I have already probably hidden info I need from the other 7 columns and the next column I filter will only filter from the previous filter put in place. So, I would like to know how to filter all 8 columns for a particular course simultaenously because filtering 1 column at a time will only be additive and will hide info I need from the other columns. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I filter multiple columns at once?
"challa prabhu" wrote: Hi, Filter by using advanced criteria The Advanced Filter command on the Data menu lets you use complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount 30000.) to filter a range, but it works differently from the AutoFilter command in several important ways. It displays the Advanced Filter dialog box instead of the Custom AutoFilter dialog box. You do not type the complex criteria in the Advanced Filter dialog box as you do in the Custom AutoFilter dialog box. Rather, you type the complex criteria in a criteria range on the worksheet and above the range you want to filter. Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the complex criteria. Although you can filter a range in place, like the AutoFilter command, the Advanced Filter command does not display drop-down lists for the columns. 1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure there is at least one blank row between the criteria values and the range. 2. In the rows below the column labels, type the criteria you want to match. 3. Click a cell in the range. 4. On the Data menu, point to Filter, and then click Advanced Filter. 5. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows. 6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog . 7. To change how the data is filtered, change the values in the criteria range and filter the data again. Challa Prabhu Hi I tried using the advanced filter function earlier and it didn't seem to do what I needed but I was probably not doing it right. I will try again cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel how can I filter multiple columns SIMULTANEOUSLY? | Excel Discussion (Misc queries) | |||
Filter based value in multiple columns | Excel Discussion (Misc queries) | |||
how do i filter multiple columns | Excel Worksheet Functions | |||
automatically filter out blanks in multiple columns | Excel Worksheet Functions | |||
how do I filter for 1 variable in multiple columns | Excel Worksheet Functions |