Count areas of numbers

[mathon]

Hi,

I have an excel-sheet with the following column of numbers

25
25
25
25
25
25
26
26
26
26
26
26
26
27
27
27
27
27
27
27
28
28
28
28
28
28
28
29
29
29
29
29
29
29
30
30
30
30
30
30
30
31
31

And I want to get the following column from the above column:

25
26
27
28
29
30
31

 

[mathon]

Is that possible? with count and then write the correponding number only once in the other column or something like that?

I appreciate every tipp.

Regards
matti

 

[Molby]

Under Data, Filter, check out the advanced filter, where you can filter out unique values to another location.

 

[mathon]

Is this not possible with functions? because the values can change.

 

[xlbo]

What is it that you are trying to achieve by this - it is doable with functions but is very inefficient - please describe what you need to do with the list of unique numbers and how the data gets into the longer list on a recurring basis - we should then be able to give you the best solution for your specific scenario

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

 

[mintjulep]

Data table -> query

 

[Fr33dan]

The Advance filter works but doesn't update. But if you put it in an _Change sub then that solves that problem

Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then
        Range("A1").EntireColumn.AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=Range("C1").EntireColumn, Unique:=True
    End If
    
End Sub

 

[SkipVought]

mathon,

Judging from your postings, you do not understand many basic Excel data analysis features.

I'd suggest becomming familiar with how Excel works and what Excel tools are available to address various kinds of problems, before you try to use VBA to do anyting.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]