Pick values from a row
 

Hi,

I have one challenge:

I want to pick values from one column and place them succesively and
automaticely in a new one - and ignore every empty cell; f.eks.

In Column A there are values/ text in row 3, 5 ,8 and 12 (all other celles
are empty). These values i want to automatically transferred to Column B in
row 1, 2, 3 and 4.

What's the formula for doing this??

Regards dagfinn

Just one way to try ..

Put in

B1:
=3DIF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A :A,SMALL(C:C,ROWS($A$1:A1=
))))

C1: =3DIF(A1=3D"","",ROW())

Select B1:C1, fill down as many rows as needed

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----

Dagfinn wrote:
Hi,

I have one challenge:

I want to pick values from one column and place them succesively and
automaticely in a new one - and ignore every empty cell; f.eks.

In Column A there are values/ text in row 3, 5 ,8 and 12 (all other
celles
are empty). These values i want to automatically transferred to
Column B in
row 1, 2, 3 and 4.
=20
What's the formula for doing this??
=20
Regards dagfinn

Hi, Sorry Max; there must be something wrong with the formula (or maybe I
used it wrong).Excel didn't want to calculate with it.

I need more information/ verification.......

Dagfinn

Max skrev:

Just one way to try ..

Put in

B1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A:A ,SMALL(C:C,ROWS($A$1:A1))))

C1: =IF(A1="","",ROW())

Select B1:C1, fill down as many rows as needed

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Dagfinn wrote:
Hi,

I have one challenge:

I want to pick values from one column and place them succesively and
automaticely in a new one - and ignore every empty cell; f.eks.

In Column A there are values/ text in row 3, 5 ,8 and 12 (all other
celles
are empty). These values i want to automatically transferred to
Column B in
row 1, 2, 3 and 4.

What's the formula for doing this??

Regards dagfinn

Hi

When you don't have a header for column A, enter it into A1.
Select all datarange in A1 (header incl.) and set autofilter on and then to
'Not Empty'
Select filtered data in column A and copy them.
Set autofilter to 'All'
select cell B2. Paste.
It's done!


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"Dagfinn" wrote in message
...
Hi,

I have one challenge:

I want to pick values from one column and place them succesively and
automaticely in a new one - and ignore every empty cell; f.eks.

In Column A there are values/ text in row 3, 5 ,8 and 12 (all other celles
are empty). These values i want to automatically transferred to Column B
in
row 1, 2, 3 and 4.

What's the formula for doing this??

Regards dagfinn

Hi,


My challenge is that I need a formula. This is not a one-time-operation...

Dagfinn

Arvi Laanemets skrev:

Hi

When you don't have a header for column A, enter it into A1.
Select all datarange in A1 (header incl.) and set autofilter on and then to
'Not Empty'
Select filtered data in column A and copy them.
Set autofilter to 'All'
select cell B2. Paste.
It's done!


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"Dagfinn" wrote in message
...
Hi,

I have one challenge:

I want to pick values from one column and place them succesively and
automaticely in a new one - and ignore every empty cell; f.eks.

In Column A there are values/ text in row 3, 5 ,8 and 12 (all other celles
are empty). These values i want to automatically transferred to Column B
in
row 1, 2, 3 and 4.

What's the formula for doing this??

Regards dagfinn

Hi

(On fly)

One way (not a formula):
You must have a header for column A (p.e. in cell A1)
Define a named range (fixed one), which includes enough rows. Like this:
YourRange=Sheet1!$A$1:$A$1000
Save the workbook.
Create an ODBC query with defined named range as source table. When asked
about it, set filter condition for query that first (and only) column values
must not be Null, and set inserting point at B1.

In query properties set the refreshing rate (periodically, or/and at start).
Set, that old data are overwritten and data from abundant rows to be cleared
(otherwise your table in column A will be messed up). Of-course changes in
column A aren't displayed immediately, but you always can select some
non-empty cell in column B and refresh the query.

Second way:
Add a helper column as A (i.e. your data column will be now B)
Into helper column enter formula (example for cell A2)
=IF($B2="","",COUNTIF($B$2:$B2,"0"))
and copy down for as much rows as you need. You can hide column A now.
Into C2 enter the formula
=IF(ISERROR(VLOOKUP(ROW()-1,$A$2:$A$1000,1,0)),"",VLOOKUP(ROW()-1,$A$2:$A$10
00,1,0))
and copy down.

Third way:
You can do it using array functions. I remember there were some postings
with such formulas in some Excel NG, but I myself haven't used them.


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"Dagfinn" wrote in message
...
Hi,


My challenge is that I need a formula. This is not a one-time-operation...

Dagfinn

Arvi Laanemets skrev:

Hi

When you don't have a header for column A, enter it into A1.
Select all datarange in A1 (header incl.) and set autofilter on and then
to
'Not Empty'
Select filtered data in column A and copy them.
Set autofilter to 'All'
select cell B2. Paste.
It's done!


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"Dagfinn" wrote in message
...
Hi,

I have one challenge:

I want to pick values from one column and place them succesively and
automaticely in a new one - and ignore every empty cell; f.eks.

In Column A there are values/ text in row 3, 5 ,8 and 12 (all other
celles
are empty). These values i want to automatically transferred to Column
B
in
row 1, 2, 3 and 4.

What's the formula for doing this??

Regards dagfinn

Strange, it should work ok, with col B returning
the results you want (just tested it again)

Maybe check the calc mode's at "Manual"
Press F9, does it compute ?
Click Tools Options Calc tab
Check "Automatic" OK

If you want, I can send you a sample book via private email
Just post a "readable" email add in response here
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Dagfinn wrote in message
...
Hi, Sorry Max; there must be something wrong with the formula (or maybe I
used it wrong).Excel didn't want to calculate with it.

I need more information/ verification.......

Dagfinn

One more thought, might be a "language" issue?
Perhaps try changing the commas in the formula
to semi-colons to suit your Excel language
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Dagfinn wrote in message
...
Hi, Sorry Max; there must be something wrong with the formula (or maybe I
used it wrong).Excel didn't want to calculate with it.

I need more information/ verification.......

Dagfinn

Oops ..
Maybe check the calc mode's at "Manual"

should actually read as:
Maybe check the calc mode's not at "Manual"
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---

Let column A from A3 on house the sample you provided.

Enter 0 in B2.

In B3 enter & copy down:

=IF(A3<"",LOOKUP(9.99999999999999E+307,$B$2:B2)+1 ,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:B)

In C3 enter & copy down:

=IF(ROW()-ROW(C$3)+1<=$C$1,LOOKUP(ROW()-ROW(C$3)+1,B:B,A:A),"")

"Dagfinn" wrote in message
...
Hi,

I have one challenge:

I want to pick values from one column and place them succesively and
automaticely in a new one - and ignore every empty cell; f.eks.

In Column A there are values/ text in row 3, 5 ,8 and 12 (all other celles
are empty). These values i want to automatically transferred to Column B
in
row 1, 2, 3 and 4.

What's the formula for doing this??

Regards dagfinn

Dagfinn, try this formula. Paste into cell B1, press F2 then
CTRL+SHIFT+ENTER. Then copy the formula down as far as you want.

=IF(INDEX($A:$A,SMALL(IF($A$1:$A$65535<"",ROW($A$ 1:$A$65535),65536),ROW()+1-ROW(B$1)))=0,"",INDEX($A:$A,SMALL(IF($A$1:$A$65535 <"",ROW($A$1:$A$65535),65536),ROW()+1-ROW(B$1))))

After that, if you want to start the list on another row just edit the 2
instances of ROW(B$1) by changing the absolute row number from 1 to the new
starting row number.

Hope you like it. Merry Christmas!


"Dagfinn" wrote:

Hi,

I have one challenge:

I want to pick values from one column and place them succesively and
automaticely in a new one - and ignore every empty cell; f.eks.

In Column A there are values/ text in row 3, 5 ,8 and 12 (all other celles
are empty). These values i want to automatically transferred to Column B in
row 1, 2, 3 and 4.

What's the formula for doing this??

Regards dagfinn