Extracting values from a column to a new column

[Rolfie]

I have my text (or number) values organized in a singel column looking like
this


A
1 Lemon
2 Apple
3 ""
4 Orange
5 ""
6 Pear


What formula (not VBA) should I use to have it arranged like this in a new
column?
Note - the cells with a value should retain it's relative position in the
column

B
1 Lemon
2 Apple
3 Orange
4 Pear
5 ""
6 ""


Furthermore, is it possible, if needed, to sort the values in ascending or
descending order in the new column?


Tia
Rolfie

[Nick Hodge]

Rolfie

Sure

Go to Tools>Options>Custom Lists and then either enter, or better still
'import' the list from a range on the spreadsheet. (Do not include the
blanks, these will sort to the end automatically)

Now go to Data>Sort...>Options and in the 'first sort key' dropdown, select
you new list and any other options you want here and ok out of the dialogs.
You can sort Ascending or descending as normal

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Rolfie" <-@-> wrote in message
news:%23ZPPWaVYGHA.4432@TK2MSFTNGP04.phx.gbl...
>I have my text (or number) values organized in a singel column looking like
>this
>
>
> A
> 1 Lemon
> 2 Apple
> 3 ""
> 4 Orange
> 5 ""
> 6 Pear
>
>
> What formula (not VBA) should I use to have it arranged like this in a new
> column?
> Note - the cells with a value should retain it's relative position in the
> column
>
> B
> 1 Lemon
> 2 Apple
> 3 Orange
> 4 Pear
> 5 ""
> 6 ""
>
>
> Furthermore, is it possible, if needed, to sort the values in ascending or
> descending order in the new column?
>
>
> Tia
> Rolfie
>

[Rolfie]

Thanks Nick,

but I was rather looking for a function to do this. Not manually
sort the column.

Sorry if I did'nt make myself clear.

Rolfie


Nick Hodge wrote:
|| Rolfie
||
|| Sure
||
|| Go to Tools>Options>Custom Lists and then either enter, or better
|| still 'import' the list from a range on the spreadsheet. (Do not
|| include the blanks, these will sort to the end automatically)
||
|| Now go to Data>Sort...>Options and in the 'first sort key' dropdown,
|| select you new list and any other options you want here and ok out
|| of the dialogs. You can sort Ascending or descending as normal
||
|| --
|| HTH
|| Nick Hodge
|| Microsoft MVP - Excel
|| Southampton, England
|| www.nickhodge.co.uk
|| nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
||
||
|| "Rolfie" <-@-> wrote in message
|| news:%23ZPPWaVYGHA.4432@TK2MSFTNGP04.phx.gbl...
||| I have my text (or number) values organized in a singel column
||| looking like this
|||
|||
||| A
||| 1 Lemon
||| 2 Apple
||| 3 ""
||| 4 Orange
||| 5 ""
||| 6 Pear
|||
|||
||| What formula (not VBA) should I use to have it arranged like this
||| in a new column?
||| Note - the cells with a value should retain it's relative position
||| in the column
|||
||| B
||| 1 Lemon
||| 2 Apple
||| 3 Orange
||| 4 Pear
||| 5 ""
||| 6 ""
|||
|||
||| Furthermore, is it possible, if needed, to sort the values in
||| ascending or descending order in the new column?
|||
|||
||| Tia
||| Rolfie

[Ardus Petus]

Not quite perfect:

Extra empty row above data
staging column B with formula:
=(A2<>"")*(MAX(B$1:B1)+1)
results in column C: with formula:
=IF(ISNA(MATCH(ROW()-1,B$2:B$7,0)),"",INDEX(A:A,MATCH(ROW()-1,B$2:B$7,0)+1))

Some wise guy will certainly offer a solution without staging column!

See example: http://cjoint.com/?eqscpQ1bt3

HTH
--
AP

"Rolfie" <-@-> a écrit dans le message de
news:%236pFLdWYGHA.508@TK2MSFTNGP02.phx.gbl...
> Thanks Nick,
>
> but I was rather looking for a function to do this. Not manually
> sort the column.
>
> Sorry if I did'nt make myself clear.
>
> Rolfie
>
>
> Nick Hodge wrote:
> || Rolfie
> ||
> || Sure
> ||
> || Go to Tools>Options>Custom Lists and then either enter, or better
> || still 'import' the list from a range on the spreadsheet. (Do not
> || include the blanks, these will sort to the end automatically)
> ||
> || Now go to Data>Sort...>Options and in the 'first sort key' dropdown,
> || select you new list and any other options you want here and ok out
> || of the dialogs. You can sort Ascending or descending as normal
> ||
> || --
> || HTH
> || Nick Hodge
> || Microsoft MVP - Excel
> || Southampton, England
> || www.nickhodge.co.uk
> || nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> ||
> ||
> || "Rolfie" <-@-> wrote in message
> || news:%23ZPPWaVYGHA.4432@TK2MSFTNGP04.phx.gbl...
> ||| I have my text (or number) values organized in a singel column
> ||| looking like this
> |||
> |||
> ||| A
> ||| 1 Lemon
> ||| 2 Apple
> ||| 3 ""
> ||| 4 Orange
> ||| 5 ""
> ||| 6 Pear
> |||
> |||
> ||| What formula (not VBA) should I use to have it arranged like this
> ||| in a new column?
> ||| Note - the cells with a value should retain it's relative position
> ||| in the column
> |||
> ||| B
> ||| 1 Lemon
> ||| 2 Apple
> ||| 3 Orange
> ||| 4 Pear
> ||| 5 ""
> ||| 6 ""
> |||
> |||
> ||| Furthermore, is it possible, if needed, to sort the values in
> ||| ascending or descending order in the new column?
> |||
> |||
> ||| Tia
> ||| Rolfie
>
>

[Ardus Petus]

Result formula should be:
=IF(ISNA(MATCH(ROW()-1,B:B,0)),"",INDEX(A:A,MATCH(ROW()-1,B:B,0)))
to make it independent of # of data rows

HTH
--
AP




"Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de
news:uiVgM9WYGHA.4684@TK2MSFTNGP03.phx.gbl...
> Not quite perfect:
>
> Extra empty row above data
> staging column B with formula:
> =(A2<>"")*(MAX(B$1:B1)+1)
> results in column C: with formula:
>
=IF(ISNA(MATCH(ROW()-1,B$2:B$7,0)),"",INDEX(A:A,MATCH(ROW()-1,B$2:B$7,0)+1))
>
> Some wise guy will certainly offer a solution without staging column!
>
> See example: http://cjoint.com/?eqscpQ1bt3
>
> HTH
> --
> AP
>
> "Rolfie" <-@-> a écrit dans le message de
> news:%236pFLdWYGHA.508@TK2MSFTNGP02.phx.gbl...
> > Thanks Nick,
> >
> > but I was rather looking for a function to do this. Not manually
> > sort the column.
> >
> > Sorry if I did'nt make myself clear.
> >
> > Rolfie
> >
> >
> > Nick Hodge wrote:
> > || Rolfie
> > ||
> > || Sure
> > ||
> > || Go to Tools>Options>Custom Lists and then either enter, or better
> > || still 'import' the list from a range on the spreadsheet. (Do not
> > || include the blanks, these will sort to the end automatically)
> > ||
> > || Now go to Data>Sort...>Options and in the 'first sort key' dropdown,
> > || select you new list and any other options you want here and ok out
> > || of the dialogs. You can sort Ascending or descending as normal
> > ||
> > || --
> > || HTH
> > || Nick Hodge
> > || Microsoft MVP - Excel
> > || Southampton, England
> > || www.nickhodge.co.uk
> > || nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> > ||
> > ||
> > || "Rolfie" <-@-> wrote in message
> > || news:%23ZPPWaVYGHA.4432@TK2MSFTNGP04.phx.gbl...
> > ||| I have my text (or number) values organized in a singel column
> > ||| looking like this
> > |||
> > |||
> > ||| A
> > ||| 1 Lemon
> > ||| 2 Apple
> > ||| 3 ""
> > ||| 4 Orange
> > ||| 5 ""
> > ||| 6 Pear
> > |||
> > |||
> > ||| What formula (not VBA) should I use to have it arranged like this
> > ||| in a new column?
> > ||| Note - the cells with a value should retain it's relative position
> > ||| in the column
> > |||
> > ||| B
> > ||| 1 Lemon
> > ||| 2 Apple
> > ||| 3 Orange
> > ||| 4 Pear
> > ||| 5 ""
> > ||| 6 ""
> > |||
> > |||
> > ||| Furthermore, is it possible, if needed, to sort the values in
> > ||| ascending or descending order in the new column?
> > |||
> > |||
> > ||| Tia
> > ||| Rolfie
> >
> >
>
>

[Domenic]

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

Sorted, by relative position..

B1, copied down:

=IF(ROWS(B$1:B1)<=COUNTIF(A$1:A$6,"?*"),INDEX(A$1:A$6,SMALL(IF(A$1:A$6<>"
",ROW(A$1:A$6)-ROW(A$1)+1),ROWS(B$1:B1))),"")

Sorted, in ascending order...

B1, copied down:

=IF(ROWS(B$1:B1)<=COUNTIF(A$1:A$6,"?*"),INDEX(A$1:A$6,MATCH(SMALL(IF(A$1:
A$6<>"",COUNTIF(A$1:A$6,"<"&A$1:A$6)+ROW(A$1:A$6)/10^5),ROWS(B$1:B1)),COU
NTIF(A$1:A$6,"<"&A$1:A$6)+ROW(A$1:A$6)/10^5,0)),"")

Hope this helps!

In article <#ZPPWaVYGHA.4432@TK2MSFTNGP04.phx.gbl>, "Rolfie" <-@->
wrote:

> I have my text (or number) values organized in a singel column looking like
> this
>
>
> A
> 1 Lemon
> 2 Apple
> 3 ""
> 4 Orange
> 5 ""
> 6 Pear
>
>
> What formula (not VBA) should I use to have it arranged like this in a new
> column?
> Note - the cells with a value should retain it's relative position in the
> column
>
> B
> 1 Lemon
> 2 Apple
> 3 Orange
> 4 Pear
> 5 ""
> 6 ""
>
>
> Furthermore, is it possible, if needed, to sort the values in ascending or
> descending order in the new column?
>
>
> Tia
> Rolfie