Creating a list of job numbers, missing out irrelevant items

[DDawson]

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

 

[Stephen]

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.

 

[Max]

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,SMALL(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.

 

[DDawson]

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

 

[DDawson]

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]

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

 

[DDawson]

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]

Ok, I got you. Here's the complete set-up
which will deliver the exact results that you're after

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

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

In Sheet2,

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

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))

In C2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!C:C,SMALL(A:A,ROWS($1:1))))

In D2:
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",B2+ROW()/10^10))
Leave D1 blank
(This is the additional criteria col to filter out duplicates and
prepare for an ascending sort for the final results in cols E and F)

In E2:
=IF(ROWS($1:1)>COUNT($D:$D),"",INDEX(B:B,MATCH(SMALL($D:$D,ROWS($1:1)),$D:$D,0)))
Copy E2 to F2. Label E1:F1 as: Job No, Option

Select A2:F2, copy down to cover the max expected extent of data in Sheet1.
Hide away cols A to D. Cols E & F will return the required results
dynamically, with all results neatly bunched at the top.

 

[DDawson]

Dear Max

Thank you for following up on this, I will try it out today.

Have a nice week and a merry Christmas - when it comes.

Kind regards
Dylan

 

[Max]

Welcome, Dylan
Best wishes for a Merry Christmas