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


--
slim
------------------------------------------------------------------------
slim's Profile: http://www.excelforum.com/member.php...o&userid=28643
View this thread: http://www.excelforum.com/showthread...hreadid=543221

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:


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


--
slim
------------------------------------------------------------------------
slim's Profile: http://www.excelforum.com/member.php...o&userid=28643
View this thread: http://www.excelforum.com/showthread...hreadid=543221

Hi, Thanks for replying.

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

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

Hope this clarifies.


--
slim
------------------------------------------------------------------------
slim's Profile: http://www.excelforum.com/member.php...o&userid=28643
View this thread: http://www.excelforum.com/showthread...hreadid=543221

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:


Hi, Thanks for replying.

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

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

Hope this clarifies.


--
slim
------------------------------------------------------------------------
slim's Profile: http://www.excelforum.com/member.php...o&userid=28643
View this thread: http://www.excelforum.com/showthread...hreadid=543221

On Thu, 18 May 2006 04:16:38 -0500, slim
wrote:


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