I have a sheet that will potentially use all 60 odd thousand rows. One column
will be "Job" and I want a list of unique jobs to be generated from this
column.

Currently I'd have a countif along the lines of
=IF(COUNTIF($A$1:A13,A14)0,"",MAX($B$1:B13)+1)
copied all the way down, then use the numbers to index/match and pull off a
list of jobs.

To have this running down the entire sheet would take a bit of processing
power (maybe not alot, but certainlly more than I want it to if possible) and
it occurs to me that if I could find a formula that finds the next unique
match in an array (compaired to another array) that this would help lots.

=VLOOKUP(<A1:A10,Sheet1!A1:A65536,1,FALSE) for example.

Is there a reason you can't use Advanced Filter to create a list of the
unique names for you?

With your list is in A1:A60000, with A1 as the heading: Job

D1: Job (the same col heading as A1)

Select your list (A1:A60000)

<Data<Filter<Advanced Filter
List Range: (already selected $A$1:$A$100)
Criteria Range: (leave this blank)
Check: Unique records only
Check: Copy to another location
Copy to: D1 (which contains the col heading Job)

Click the [OK] button

The list of unique job values will be created under D1

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"PaulW" wrote:

I have a sheet that will potentially use all 60 odd thousand rows. One column
will be "Job" and I want a list of unique jobs to be generated from this
column.

Currently I'd have a countif along the lines of
=IF(COUNTIF($A$1:A13,A14)0,"",MAX($B$1:B13)+1)
copied all the way down, then use the numbers to index/match and pull off a
list of jobs.

To have this running down the entire sheet would take a bit of processing
power (maybe not alot, but certainlly more than I want it to if possible) and
it occurs to me that if I could find a formula that finds the next unique
match in an array (compaired to another array) that this would help lots.

=VLOOKUP(<A1:A10,Sheet1!A1:A65536,1,FALSE) for example.

The only reason is that I wanted the list to constantly be correct, so when
new jobs are added, the list automatically includes them.

But, since I'll be using a macro to copy information into this sheet, then
copy the list of jobs out, I don't see why I can't just add the creation of
an Advanced Filter into the macro. Never used one before, cheers for the help.

"Ron Coderre" wrote:

Is there a reason you can't use Advanced Filter to create a list of the
unique names for you?