I have a table with the headings; job number, title, and option (A/E).
I want to pull in the jobs which are only option A into a new worksheet table.

So my first table may have

Job No Title Option
1234 Site 01 A
2345 Site 02 E
3456 Site 03 E
4567 Site 04 A

My new table should only have:

Job No
1234
4567

Kind regards
Dylan

Easiest way would be to copy the worksheet, then sort the new one by "option
column" and then delete the rows you don't want.

"DDawson" wrote in message
...
I have a table with the headings; job number, title, and option (A/E).
I want to pull in the jobs which are only option A into a new worksheet
table.

So my first table may have

Job No Title Option
1234 Site 01 A
2345 Site 02 E
3456 Site 03 E
4567 Site 04 A

My new table should only have:

Job No
1234
4567

Kind regards
Dylan

Another way which drives it out auto in another sheet

Assume source data as posted in Sheet1's cols A to C, from row2 down

In Sheet2,
Put in A2:
=IF(Sheet1!C2="A",ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in Sheet1.
Hide away col A. Col B will return the required results, all neatly bunched
at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DDawson" wrote:
I have a table with the headings; job number, title, and option (A/E).
I want to pull in the jobs which are only option A into a new worksheet table.

So my first table may have

Job No Title Option
1234 Site 01 A
2345 Site 02 E
3456 Site 03 E
4567 Site 04 A

My new table should only have:

Job No
1234
4567

Kind regards
Dylan

Thanks Max, this kinda does what I want.

Sheet one contains the time people spend on each project and, so it may have
two or three people's time to one job number. I need to remove the duplicates
in sheet two, so that I have a column showing what jobs have Option E work to
be payed.

I'm sorry, I didn't explain this at the beginning.

Dylan

"Max" wrote:

Another way which drives it out auto in another sheet

Assume source data as posted in Sheet1's cols A to C, from row2 down

In Sheet2,
Put in A2:
=IF(Sheet1!C2="A",ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in Sheet1.
Hide away col A. Col B will return the required results, all neatly bunched
at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DDawson" wrote:
I have a table with the headings; job number, title, and option (A/E).
I want to pull in the jobs which are only option A into a new worksheet table.

So my first table may have

Job No Title Option
1234 Site 01 A
2345 Site 02 E
3456 Site 03 E
4567 Site 04 A

My new table should only have:

Job No
1234
4567

Kind regards
Dylan

Thanks Max, this kinda does what I want.

Sheet one contains the time people spend on each project and, so it may have
two or three people's time to one job number. I need to remove the
duplicates
in sheet two, so that I have a column showing what jobs have Option E work
to
be payed.

Also, is there a way to sort the job numbers ascending?

I'm sorry, I didn't explain this at the beginning.

Dylan

"Max" wrote:

Another way which drives it out auto in another sheet

Assume source data as posted in Sheet1's cols A to C, from row2 down

In Sheet2,
Put in A2:
=IF(Sheet1!C2="A",ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in Sheet1.
Hide away col A. Col B will return the required results, all neatly bunched
at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DDawson" wrote:
I have a table with the headings; job number, title, and option (A/E).
I want to pull in the jobs which are only option A into a new worksheet table.

So my first table may have

Job No Title Option
1234 Site 01 A
2345 Site 02 E
3456 Site 03 E
4567 Site 04 A

My new table should only have:

Job No
1234
4567

Kind regards
Dylan

I'm afraid I lost you in your expanded scope

.. remove the duplicates in sheet two
what are the duplicates in the source table to be removed?

.. what jobs have Option E
now its option E that you're interested in, not option A?

Pl elaborate and illustrate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DDawson" wrote in message
...
Thanks Max, this kinda does what I want.

Sheet one contains the time people spend on each project and, so it may
have
two or three people's time to one job number. I need to remove the
duplicates
in sheet two, so that I have a column showing what jobs have Option E work
to
be payed.

Also, is there a way to sort the job numbers ascending?

I'm sorry, I didn't explain this at the beginning.

Job No Option
1234 E
2345 A
3456 E
2345 A
3456 A
4567 E

To Avoid Duplicates, so that ...

Job No Option
2345 A
3456 A

is shown, rather than...

Job No Option
2345 A
2345 A
3456 A


"Max" wrote:

I'm afraid I lost you in your expanded scope

.. remove the duplicates in sheet two
what are the duplicates in the source table to be removed?

.. what jobs have Option E
now its option E that you're interested in, not option A?

Pl elaborate and illustrate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DDawson" wrote in message
...
Thanks Max, this kinda does what I want.

Sheet one contains the time people spend on each project and, so it may
have
two or three people's time to one job number. I need to remove the
duplicates
in sheet two, so that I have a column showing what jobs have Option E work
to
be payed.

Also, is there a way to sort the job numbers ascending?

I'm sorry, I didn't explain this at the beginning.