Sorting by Two Columns

L

Luke Slotwinski

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
 
S

Stefi

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:
 
L

Luke Slotwinski

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
 
S

Stefi

I thought my solution will just do it! Post some example data with the
required result!
Stefi


„Luke Slotwinski†ezt írta:
 
L

Luke Slotwinski

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
 
S

Stefi

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:
 
S

Stefi

You are welcome! Thanks for the feedback!
Stefi

Luke Slotwinski said:
Stefi:

That did exactly what I was looking for...Thank you so much for your help!!

Luke Slotwinski
Click to expand...
 
Top