Automatic Sorting

[Guest]

I have 2 columns in my sheet. Column A is a list of job tasks needed to be completed. Column B is where I type an "x" when each task is completed. The list of job tasks is really long now and I want to be able to automatically sort out which tasks are uncompleted (ie. do not have an "x" in column B) on a seperate sheet so I can print out just the list of uncompleted tasks all bundled together.

Example
What I have on sheet 1...
A
1 Task 1
2 Task
3 Task 3
4 Task 4
5 Task
6 Task 6

What I want on sheet 2...
A
1 Task
2 Task
3 Task 5

and if I mark task 3 as completed on sheet 1, it will automatically disappear on sheet 2 and Task 5 will move up to row 2

I know how to do it by copying, pasting and re-sorting each time but want to find an automatic solution if possible.
many thanks

 

[Guest]

Try this set-u

In Sheet
----------

Put

in A1: =OFFSET($C$1,MATCH(LARGE(C:C,ROW()),C:C,0)-1,-1
in B1: =IF(ISBLANK(Sheet1!B1),Sheet1!A1,0
in C1: =IF(B1=0,0,50000-ROW()

Select A1:C

Copy down to say, row100

[assuming the max. # of tasks in Sheet1 is <=100
If the max # is >1000, just copy down more row
until a "safe" max number

Hide cols B:

Suppress zero values from showing
Click Tools > Options > View tab > Uncheck "Zero values" > O

In col A will be the desired result

-
Rgd
Ma
xl 9
-
Please respond, in newsgrou
xdemechanik <at>yahoo<dot>co

----- Dave wrote: ----

I have 2 columns in my sheet. Column A is a list of job tasks needed to be completed. Column B is where I type an "x" when each task is completed. The list of job tasks is really long now and I want to be able to automatically sort out which tasks are uncompleted (ie. do not have an "x" in column B) on a seperate sheet so I can print out just the list of uncompleted tasks all bundled together.

Example
What I have on sheet 1...
A
1 Task 1
2 Task
3 Task 3
4 Task 4
5 Task
6 Task 6

What I want on sheet 2...
A
1 Task
2 Task
3 Task 5

and if I mark task 3 as completed on sheet 1, it will automatically disappear on sheet 2 and Task 5 will move up to row 2

I know how to do it by copying, pasting and re-sorting each time but want to find an automatic solution if possible.
many thanks

 

[Frank Kabel]

Hi
put the following in cell A1 on your second sheet
=INDEX('sheet1'!$A$1:$A$1000,SMALL(IF('sheet1'!$B$1:$B$1000="",ROW('she
et1'!$B$1:$B$1000),1001),ROW()))

enter this as array formula (with CTRL+SHIFT+ENTER) and copy down.
Note: this assumes both ranges start in row 1

--
Regards
Frank Kabel
Frankfurt, Germany

I have 2 columns in my sheet. Column A is a list of job tasks needed

to be completed. Column B is where I type an "x" when each task is
completed. The list of job tasks is really long now and I want to be
able to automatically sort out which tasks are uncompleted (ie. do not
have an "x" in column B) on a seperate sheet so I can print out just
the list of uncompleted tasks all bundled together.

Example:
What I have on sheet 1...
A B
1 Task 1 X
2 Task 2
3 Task 3
4 Task 4 X
5 Task 5
6 Task 6 X

What I want on sheet 2...
A B
1 Task 2
2 Task 3
3 Task 5

and if I mark task 3 as completed on sheet 1, it will automatically

disappear on sheet 2 and Task 5 will move up to row 2.

I know how to do it by copying, pasting and re-sorting each time but

want to find an automatic solution if possible.

 

[Max]

Try this set-up

In Sheet2
-----------

Put:

in A1: =OFFSET($C$1,MATCH(LARGE(C:C,ROW()),C:C,0)-1,-1)
in B1: =IF(ISBLANK(Sheet1!B1),Sheet1!A1,0)
in C1: =IF(B1=0,0,50000-ROW())

Select A1:C1

Copy down to say, row1000

[assuming the max. # of tasks in Sheet1 is <=1000
If the max # is >1000, just copy down more rows
until a "safe" max number]

Hide cols B:C

Suppress zero values from showing:
Click Tools > Options > View tab > Uncheck "Zero values" >
Ok

In col A will be the desired results

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com

----- Dave wrote: -----

-----Original Message-----
I have 2 columns in my sheet. Column A is a list of job

tasks needed to be completed. Column B is where I type
an "x" when each task is completed. The list of job tasks
is really long now and I want to be able to automatically
sort out which tasks are uncompleted (ie. do not have
an "x" in column B) on a seperate sheet so I can print out
just the list of uncompleted tasks all bundled together.

Example:
What I have on sheet 1...
A B
1 Task 1 X
2 Task 2
3 Task 3
4 Task 4 X
5 Task 5
6 Task 6 X

What I want on sheet 2...
A B
1 Task 2
2 Task 3
3 Task 5

and if I mark task 3 as completed on sheet 1, it will

automatically disappear on sheet 2 and Task 5 will move up
to row 2.

I know how to do it by copying, pasting and re-sorting

each time but want to find an automatic solution if
possible.