Dynamic List of Unique Records

[eksterd]

Hi,

I have in sheet 1 a range of 100 records which is growing. I would like to summarise the 100 records into a uniquel list in sheet 2 using a VBA code through use of an update event. the data i would like to summarise in a list is in column A in sheet 1. the target in sheet 2 would also be column A.

I could pivot table the data or use the filter within excel, however, this is manual intervention, thus, preferring the code.

any thoughts

[Bob Phillips]

No VBA in sight

[Dadmin]

I want to use a PT for statistics, anyway. However, when I add new, unique records to my database they appear at the end of the PT instead of in sorted order.

In the attached example, I created four records and then created a 'Statistics' PT. Next, I added a new record to my database ("Ref C") using the built-in DataForm dialog box. But, when I refresh the PT, the new record appears at the end. I want the PT to resort, putting the new record in sorted order. How can I do this without deleting the PT and creating a new one?

Thanks.

[Krishna Kumar]

Right click on Ref field> Field Settings> Advanced> Check on Ascending.

HTH

[Krishna Kumar]

Hi,

I have in sheet 1 a range of 100 records which is growing. I would like to summarise the 100 records into a uniquel list in sheet 2 using a VBA code through use of an update event. the data i would like to summarise in a list is in column A in sheet 1. the target in sheet 2 would also be column A.

I could pivot table the data or use the filter within excel, however, this is manual intervention, thus, preferring the code.

any thoughts

Welcome to board!!

Try,

sheet1 module;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Target.Column <> 1 Then Exit Sub
Set r = Range("a1:a" & Target.Row)
With r
.AdvancedFilter xlFilterCopy, , Sheets("Sheet2").Range("A1"), True
End With
End Sub

HTH

[Dadmin]

Right click on Ref field> Field Settings> Advanced> Check on Ascending.

HTH

Thank you. That's the setting I needed but couldn't find.:clap: