Formula to sort text values with spaces

[slim]

Hi,

I have a list of entries in a worksheet that i need to format into a
smaller list of unique entries.

The values that I want to use appear like this with blank rows between
each entry that I want to use.

Text1


Text2





Text3



Text4

Is there any formula i can use to return these in 4 cells beneath
eachother rather than with the spaces:

Text1
Text2
Text3
Text4

I want to do this using a formula so that i don't have to sort the data
every time I update the spreadsheet.

Thanks in advance

Jim

 

[Guest]

From your example it seems that you don't really need sorting, because Text1,
Text2..., are originally in this order, rather you need hiding blank rows. If
so, try to use Autofilter on column containing Text1, Text2..., the choose
Not empty option under the dropdown arrow!

Regards,
Stefi

„slim†ezt írta:

 

[slim]

Hi, Thanks for replying.

I can't just hide the cells because they are part of a data range to
so hiding the cells won't help.

The reason I need to get the values in this order is so that I can pu
them into a listbox without there being massive spaces between eac
entry.

Hope this clarifies

 

[Guest]

I have no other solution than copying Text1, Text2..., values (say in column
A) into a separate helper column (say column C) with such a macro:


Sub CopyNonEmpty()
Columns("A:A").Select 'copy from column A
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy
Columns("C:C").Select 'copy into column C
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
End Sub

Adjust column references to your real needs!


Regards,
Stefi

„slim†ezt írta:

 

[Ron Rosenfeld]

Hi,

I have a list of entries in a worksheet that i need to format into a
smaller list of unique entries.

The values that I want to use appear like this with blank rows between
each entry that I want to use.

Text1


Text2





Text3



Text4

Is there any formula i can use to return these in 4 cells beneath
eachother rather than with the spaces:

Text1
Text2
Text3
Text4

I want to do this using a formula so that i don't have to sort the data
every time I update the spreadsheet.

Thanks in advance

Jim

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=INDEX(UNIQUEVALUES(rng,1),ROWS($1:1))

and copy/drag down as far as required.

( rng is the range of entries of your data table, e.g. $A$2:$A$15 )


--ron