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" wrote:

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,SMAL L(A:A,ROWS($1:1))))

In C2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(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(SMA LL($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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DDawson" wrote:
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