Function to get all unique elements

[christhedonstar]

Hi,

What Excel function is there that takes in a range of peoples names that will only return the unique ones and spit it out on the sheet, a bit like advanced filter?

Can you make an advanced filter persist and update on calculate?

Thanks,

Chris

 

[GlennUK]

Use the Worksheet_Calculate event to run your Advanced Filter. How are you with VBA?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[christhedonstar]

I'm ok with VBA. I was thinking about writting a UDF - non volatile.

I'm scared of users getting rid of the advanced filters...

 

[xlbo]

Other option is a pivottable...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[christhedonstar]

Yeah I know I didn't want to feed results off the pivot table. I need to read a little on advanced filters. I've only ever used them for quick one off analysis. I updated the source data, hit F9 and my advanced filter results didn't update.

Oh I think I see what you're saying I need to create an advanced filter to get the results, i.e. create it every time the user calculates. I'm also not sure how the worksheet calculate would fit in the calculation dependecy tree?

 

[christhedonstar]

So I did some reading and did what you suggested. For some reason the advanced filter gets rid of any formatting and makes it go bold

wksASheet.Range("H3:H179").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("result_anchor"), Unique:=True

Is my code...

Is this normal behaviour.

 

[christhedonstar]

On top of that. The sheet calculates, then it creates the advanced filter. Since cells are driven off the advanced filter the solution means that I have to calculate twice for things to work. I thought the calculate event would do stuff before calculating cells on a worksheet.

 

[christhedonstar]

Ended up writing a proper function myself.