software

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.

Text1Text2Text3
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: www.excelforum.com/member.php...oamp;userid=28643
View this thread: www.excelforum.com/showthread...hreadid=543221From 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

?lim??ezt ?rta:

gt;
gt; Hi,
gt;
gt; I have a list of entries in a worksheet that i need to format into a
gt; smaller list of unique entries.
gt;
gt; The values that I want to use appear like this with blank rows between
gt; each entry that I want to use.
gt;
gt; Text1
gt;
gt;
gt; Text2
gt;
gt;
gt;
gt;
gt;
gt; Text3
gt;
gt;
gt;
gt; Text4
gt;
gt; Is there any formula i can use to return these in 4 cells beneath
gt; eachother rather than with the spaces:
gt;
gt; Text1
gt; Text2
gt; Text3
gt; Text4
gt;
gt; I want to do this using a formula so that i don't have to sort the data
gt; every time I update the spreadsheet.
gt;
gt; Thanks in advance
gt;
gt; Jim
gt;
gt;
gt; --
gt; slim
gt; ------------------------------------------------------------------------
gt; slim's Profile: www.excelforum.com/member.php...oamp;userid=28643
gt; View this thread: www.excelforum.com/showthread...hreadid=543221
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=28643
View this thread: www.excelforum.com/showthread...hreadid=543221I 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(quot;A:Aquot;).Select 'copy from column A
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=quot;lt;gt;quot;
Selection.Copy
Columns(quot;C:Cquot;).Select 'copy into column C
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFilter
Range(quot;A1quot;).Select
End Sub

Adjust column references to your real needs!Regards,
Stefi

?lim??ezt ?rta:

gt;
gt; Hi, Thanks for replying.
gt;
gt; I can't just hide the cells because they are part of a data range too
gt; so hiding the cells won't help.
gt;
gt; The reason I need to get the values in this order is so that I can put
gt; them into a listbox without there being massive spaces between each
gt; entry.
gt;
gt; Hope this clarifies.
gt;
gt;
gt; --
gt; slim
gt; ------------------------------------------------------------------------
gt; slim's Profile: www.excelforum.com/member.php...oamp;userid=28643
gt; View this thread: www.excelforum.com/showthread...hreadid=543221
gt;
gt;

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

gt;
gt;Hi,
gt;
gt;I have a list of entries in a worksheet that i need to format into a
gt;smaller list of unique entries.
gt;
gt;The values that I want to use appear like this with blank rows between
gt;each entry that I want to use.
gt;
gt;Text1
gt;
gt;
gt;Text2
gt;
gt;
gt;
gt;
gt;
gt;Text3
gt;
gt;
gt;
gt;Text4
gt;
gt;Is there any formula i can use to return these in 4 cells beneath
gt;eachother rather than with the spaces:
gt;
gt;Text1
gt;Text2
gt;Text3
gt;Text4
gt;
gt;I want to do this using a formula so that i don't have to sort the data
gt;every time I update the spreadsheet.
gt;
gt;Thanks in advance
gt;
gt;Jim

Download and install Longre's free morefunc.xll add-in from
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