OFFSET I think

[Geron]

H

I have a list of data in Sheet1! Column A (rows 5 to 10000) that has the formula.......=Suburb!K5, K6, K7 et
All cells in the column are not always populated with data, but I need the formula to reach the 10000 th row, because in some instances I have 10000 entries in the suburb sheet

In column A I have many duplicates, what I have done is use this formula in col B to remove the duplicates

=IF(COUNTIF($A$5:A5,A5)=1,A5,""

This works ok, but takes forever, I then need to sort the unique entries, but again this takes forever as it always looks to the whole range (A5:A10000

Is there a way to set the formula up to only look at non blank cells and can I also sort the non blank cells

Thanks for any hel

Geron

 

[Dave Peterson]

Maybe a different approach.

Instead of using formulas, you could use Data|filter|advanced filter to extract
the unique values.

Debra Dalgleish has some notes at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then you could sort that new column.

 

[Geron]

Thanks Dave

One more problem.......

I've recorded a macro to sort and filter my list, the only problem is that when I sort I can only do it by descending or all my data will start at row 10000

How do I get around this

Thanks agai

 

[Dave Peterson]

When you did the advanced filter using the unique records only checkbox, didn't
you get rid of all the blanks except for one blank value?

Something like this worked ok for me:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myToCell As Range

With ActiveSheet
Set myToCell = .Range("c1")
myToCell.EntireColumn.ClearContents

Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
myRng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=myToCell, Unique:=True

Set myRng = .Range("c1", .Cells(.Rows.Count, "C").End(xlUp))

With myRng
.Value = .Value
.Sort key1:=.Cells(1), header:=xlYes
End With
End With
End Sub

The .value = .value bit gets rid of "blank" cells that aren't empty. Cells that
contained formulas that evaluated to "" and were converted to values aren't
empty.