| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Two Columns
I designed a worksheet that has four columns. I need to sort the 400+ entries
in the worksheet by two columns. The first column (B), has entries of either A-OK, A-UNDER, A-OVER. The second column (C) has entries of N-OK, N-UNDER, N-OVER. The criteria for the sort is to show N-UNDER and A-UNDER. So I want to be able to isolate the entries in the worksheet that have A-UNDER or N-UNDER in columns B or C... also showing the values of A and D that correspond to the row in which the *-UNDER appears. There are instances where both A-UNDER and N-UNDER appear in which case I do not want duplicates showing up. Could someone lead me in the right direction for accomplishing such a task? Thank you, Luke Slotwinski |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by Two Columns
Use a 5th helper column, insert this formula in cell E2 (you have to have a
header in all the 5 column in row 1) =IF(OR(MID(B2,3,256)="OK",MID(C2,3,256)="OK"),"OK" ,IF(OR(MID(B2,3,256)="UNDER",MID(C2,3,256)="UNDER" ),"UNDER",IF(OR(MID(B2,3,256)="OVER",MID(C2,3,256) ="OVER"),"OVER",""))) Switch on autofilter then filter by column E (choose OK, ... from the dropdown list)! Regards, Stefi €žLuke Slotwinski€ť ezt Ă*rta: I designed a worksheet that has four columns. I need to sort the 400+ entries in the worksheet by two columns. The first column (B), has entries of either A-OK, A-UNDER, A-OVER. The second column (C) has entries of N-OK, N-UNDER, N-OVER. The criteria for the sort is to show N-UNDER and A-UNDER. So I want to be able to isolate the entries in the worksheet that have A-UNDER or N-UNDER in columns B or C... also showing the values of A and D that correspond to the row in which the *-UNDER appears. There are instances where both A-UNDER and N-UNDER appear in which case I do not want duplicates showing up. Could someone lead me in the right direction for accomplishing such a task? Thank you, Luke Slotwinski |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by Two Columns
Stefi:
I created the 5th helper column, however it only filters row 2. To clarify what I would like to happend... In Columns B and C I want to filter in the entries (rows) that have either column B as A-UNDR or column C as N-UNDR. If B is A-UNDR and C is N-UNDR only show one instance of it. There are 600+ entries in this spreadsheet a month, if that makes any difference. Sorry if my first post was unclear... hope you can make sense of this. Thanks, Luke Slotwinski "Stefi" wrote: Use a 5th helper column, insert this formula in cell E2 (you have to have a header in all the 5 column in row 1) =IF(OR(MID(B2,3,256)="OK",MID(C2,3,256)="OK"),"OK" ,IF(OR(MID(B2,3,256)="UNDER",MID(C2,3,256)="UNDER" ),"UNDER",IF(OR(MID(B2,3,256)="OVER",MID(C2,3,256) ="OVER"),"OVER",""))) Switch on autofilter then filter by column E (choose OK, ... from the dropdown list)! Regards, Stefi €žLuke Slotwinski€ť ezt Ă*rta: I designed a worksheet that has four columns. I need to sort the 400+ entries in the worksheet by two columns. The first column (B), has entries of either A-OK, A-UNDER, A-OVER. The second column (C) has entries of N-OK, N-UNDER, N-OVER. The criteria for the sort is to show N-UNDER and A-UNDER. So I want to be able to isolate the entries in the worksheet that have A-UNDER or N-UNDER in columns B or C... also showing the values of A and D that correspond to the row in which the *-UNDER appears. There are instances where both A-UNDER and N-UNDER appear in which case I do not want duplicates showing up. Could someone lead me in the right direction for accomplishing such a task? Thank you, Luke Slotwinski |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by Two Columns
I thought my solution will just do it! Post some example data with the
required result! Stefi €žLuke Slotwinski€ť ezt Ă*rta: Stefi: I created the 5th helper column, however it only filters row 2. To clarify what I would like to happend... In Columns B and C I want to filter in the entries (rows) that have either column B as A-UNDR or column C as N-UNDR. If B is A-UNDR and C is N-UNDR only show one instance of it. There are 600+ entries in this spreadsheet a month, if that makes any difference. Sorry if my first post was unclear... hope you can make sense of this. Thanks, Luke Slotwinski "Stefi" wrote: Use a 5th helper column, insert this formula in cell E2 (you have to have a header in all the 5 column in row 1) =IF(OR(MID(B2,3,256)="OK",MID(C2,3,256)="OK"),"OK" ,IF(OR(MID(B2,3,256)="UNDER",MID(C2,3,256)="UNDER" ),"UNDER",IF(OR(MID(B2,3,256)="OVER",MID(C2,3,256) ="OVER"),"OVER",""))) Switch on autofilter then filter by column E (choose OK, ... from the dropdown list)! Regards, Stefi €žLuke Slotwinski€ť ezt Ă*rta: I designed a worksheet that has four columns. I need to sort the 400+ entries in the worksheet by two columns. The first column (B), has entries of either A-OK, A-UNDER, A-OVER. The second column (C) has entries of N-OK, N-UNDER, N-OVER. The criteria for the sort is to show N-UNDER and A-UNDER. So I want to be able to isolate the entries in the worksheet that have A-UNDER or N-UNDER in columns B or C... also showing the values of A and D that correspond to the row in which the *-UNDER appears. There are instances where both A-UNDER and N-UNDER appear in which case I do not want duplicates showing up. Could someone lead me in the right direction for accomplishing such a task? Thank you, Luke Slotwinski |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by Two Columns
Example of the Spreadsheet:
A B C D E M12 A-OK N-OK F-ICU 11/1/06 M15 A-UNDR N-OK F-ER 11/1/06 M17 A-OK N-UNDR F-IDC 11/1/06 M29 A-UNDR N-UNDR F-8A 11/1/06 This is about how the whole spreadsheet looks, with 600+ rows. I want to pull out the rows that have *-UNDR in them. But like M29 which has both B and C as UNDR I only want to see one instance of it. And the ones that are OK for both, like M12 I dont even want to see in the filter. Thank you so much for your help, Luke "Stefi" wrote: I thought my solution will just do it! Post some example data with the required result! Stefi €žLuke Slotwinski€ť ezt Ă*rta: Stefi: I created the 5th helper column, however it only filters row 2. To clarify what I would like to happend... In Columns B and C I want to filter in the entries (rows) that have either column B as A-UNDR or column C as N-UNDR. If B is A-UNDR and C is N-UNDR only show one instance of it. There are 600+ entries in this spreadsheet a month, if that makes any difference. Sorry if my first post was unclear... hope you can make sense of this. Thanks, Luke Slotwinski "Stefi" wrote: Use a 5th helper column, insert this formula in cell E2 (you have to have a header in all the 5 column in row 1) =IF(OR(MID(B2,3,256)="OK",MID(C2,3,256)="OK"),"OK" ,IF(OR(MID(B2,3,256)="UNDER",MID(C2,3,256)="UNDER" ),"UNDER",IF(OR(MID(B2,3,256)="OVER",MID(C2,3,256) ="OVER"),"OVER",""))) Switch on autofilter then filter by column E (choose OK, ... from the dropdown list)! Regards, Stefi €žLuke Slotwinski€ť ezt Ă*rta: I designed a worksheet that has four columns. I need to sort the 400+ entries in the worksheet by two columns. The first column (B), has entries of either A-OK, A-UNDER, A-OVER. The second column (C) has entries of N-OK, N-UNDER, N-OVER. The criteria for the sort is to show N-UNDER and A-UNDER. So I want to be able to isolate the entries in the worksheet that have A-UNDER or N-UNDER in columns B or C... also showing the values of A and D that correspond to the row in which the *-UNDER appears. There are instances where both A-UNDER and N-UNDER appear in which case I do not want duplicates showing up. Could someone lead me in the right direction for accomplishing such a task? Thank you, Luke Slotwinski |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by Two Columns
Entering this formula in F2 and copied down as required
=IF(OR(MID(B2,3,256)="UNDR",MID(C2,3,256)="UNDR"), "UNDR","") and autofiltering for "UNDR" in column F gave me (don!t forget that you must have a header in row 1 for correct working of Autofilter!) 1 A B C D E F 3 M15 A-UNDR N-OK F-ER 2011.01.06 UNDR 4 M17 A-OK N-UNDR F-IDC 2011.01.06 UNDR 5 M29 A-UNDR N-UNDR F-8A 2011.01.06 UNDR But I still don't understand what you mean by "But like M29 which has both B and C as UNDR I only want to see one instance of it"! One instance is in column B, the other instance is in column C, for seeing only one of them you have to hide one of these columns, but in this case values of this hidden column will be hidden in all other rows as well! Regards, Stefi €žLuke Slotwinski€ť ezt Ă*rta: Example of the Spreadsheet: A B C D E M12 A-OK N-OK F-ICU 11/1/06 M15 A-UNDR N-OK F-ER 11/1/06 M17 A-OK N-UNDR F-IDC 11/1/06 M29 A-UNDR N-UNDR F-8A 11/1/06 This is about how the whole spreadsheet looks, with 600+ rows. I want to pull out the rows that have *-UNDR in them. But like M29 which has both B and C as UNDR I only want to see one instance of it. And the ones that are OK for both, like M12 I dont even want to see in the filter. Thank you so much for your help, Luke "Stefi" wrote: I thought my solution will just do it! Post some example data with the required result! Stefi €žLuke Slotwinski€ť ezt Ă*rta: Stefi: I created the 5th helper column, however it only filters row 2. To clarify what I would like to happend... In Columns B and C I want to filter in the entries (rows) that have either column B as A-UNDR or column C as N-UNDR. If B is A-UNDR and C is N-UNDR only show one instance of it. There are 600+ entries in this spreadsheet a month, if that makes any difference. Sorry if my first post was unclear... hope you can make sense of this. Thanks, Luke Slotwinski "Stefi" wrote: Use a 5th helper column, insert this formula in cell E2 (you have to have a header in all the 5 column in row 1) =IF(OR(MID(B2,3,256)="OK",MID(C2,3,256)="OK"),"OK" ,IF(OR(MID(B2,3,256)="UNDER",MID(C2,3,256)="UNDER" ),"UNDER",IF(OR(MID(B2,3,256)="OVER",MID(C2,3,256) ="OVER"),"OVER",""))) Switch on autofilter then filter by column E (choose OK, ... from the dropdown list)! Regards, Stefi €žLuke Slotwinski€ť ezt Ă*rta: I designed a worksheet that has four columns. I need to sort the 400+ entries in the worksheet by two columns. The first column (B), has entries of either A-OK, A-UNDER, A-OVER. The second column (C) has entries of N-OK, N-UNDER, N-OVER. The criteria for the sort is to show N-UNDER and A-UNDER. So I want to be able to isolate the entries in the worksheet that have A-UNDER or N-UNDER in columns B or C... also showing the values of A and D that correspond to the row in which the *-UNDER appears. There are instances where both A-UNDER and N-UNDER appear in which case I do not want duplicates showing up. Could someone lead me in the right direction for accomplishing such a task? Thank you, Luke Slotwinski |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I link two columns for sorting? | Excel Discussion (Misc queries) | |||
| Sorting Excel columns in several worksheets at once. | Excel Discussion (Misc queries) | |||
| Macro sorting by 4 columns | Excel Discussion (Misc queries) | |||
| Sorting columns with expressions | New Users to Excel | |||
| sorting data across multiple columns | Excel Discussion (Misc queries) | |||
Hybrid Mode
