Excell function

D

Daly

I have two columns :
size quantity
0 0
120 2
0 0
140 1
220 2

how can i convert these 2 columns in only one without 0 value and display
the value as shown below :
120
120
140
220
220
 
L

Leo Heuser

Daly said:
I have two columns :
size quantity
0 0
120 2
0 0
140 1
220 2

how can i convert these 2 columns in only one without 0 value and display
the value as shown below :
120
120
140
220
220
Click to expand...


Daly

Here's one way, assuming your data in A2 :B11 (add your own ranges).
C1 must be empty (and present!)

In C2 enter this array formula:


=IF(ROW()-ROW($C$2)>=SUM($B$2:$B$11),"-",INDEX($A$2:$A$11,MATCH(MIN(
IF(COUNTA($C$1:C1)<MMULT((ROW($B$2:$B$11)>=TRANSPOSE(ROW($B$2:$B$11)))+0,
$B$2:$B$11),MMULT((ROW($B$2:$B$11)>=TRANSPOSE(ROW($B$2:$B$11)))+0,$B$2:$B$11))),
MMULT((ROW($B$2:$B$11)>=TRANSPOSE(ROW($B$2:$B$11)))+0,$B$2:$B$11),0)))

The formula must be committed with <Shift><Ctrl><Enter>,
also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself.
They are Excel's way of showing, that the formula is an array formula.

Copy C2 down until the cell contains a hyphen.
 
L

Leo Heuser

Leo Heuser said:
Daly

Here's one way, assuming your data in A2 :B11 (add your own ranges).
C1 must be empty (and present!)

In C2 enter this array formula:


=IF(ROW()-ROW($C$2)>=SUM($B$2:$B$11),"-",INDEX($A$2:$A$11,MATCH(MIN(
IF(COUNTA($C$1:C1)<MMULT((ROW($B$2:$B$11)>=TRANSPOSE(ROW($B$2:$B$11)))+0,
$B$2:$B$11),MMULT((ROW($B$2:$B$11)>=TRANSPOSE(ROW($B$2:$B$11)))+0,$B$2:$B$11))),
MMULT((ROW($B$2:$B$11)>=TRANSPOSE(ROW($B$2:$B$11)))+0,$B$2:$B$11),0)))

The formula must be committed with <Shift><Ctrl><Enter>,
also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself.
They are Excel's way of showing, that the formula is an array formula.

Copy C2 down until the cell contains a hyphen.

--
Best regards
Leo Heuser

Followup to newsgroup only please.
Click to expand...

Daly

Here's a shorter (and not so ugly :) one. Still an array formula and C1
must be empty (and present!).
Copy until the cell returns a #REF! error.

In C2:

=INDEX($A$2:$A$11,IF(ROW()-ROW($C$2)<$B$2,1,MATCH(ROW()-ROW($C$2)+1,
SUMIF(OFFSET($B$2,,,ROW(INDIRECT("1:"&ROWS($B$2:$B$11)))),"<>")+1)+1))


Leo Heuser
 
L

Leo Heuser

Daly
Here's a shorter (and not so ugly :) one. Still an array formula and C1
must be empty (and present!).
Copy until the cell returns a #REF! error.

In C2:

=INDEX($A$2:$A$11,IF(ROW()-ROW($C$2)<$B$2,1,MATCH(ROW()-ROW($C$2)+1,
SUMIF(OFFSET($B$2,,,ROW(INDIRECT("1:"&ROWS($B$2:$B$11)))),"<>")+1)+1))


Leo Heuser
Click to expand...
What I meant was:
Still an array formula and C1 need *not* be empty or present at all,
i.e. the formula may start in row 1.


Leo Heuser
 
D

Daly

Thank you Leo ,I tried your solution but it doesn't work ,the result was
right for the first cell (c1) but the others (c2:c9) were #N/A

can you help please
 
L

Leo Heuser

Daly said:
Thank you Leo ,I tried your solution but it doesn't work ,the result was
right for the first cell (c1) but the others (c2:c9) were #N/A

can you help please
Click to expand...

Daly

The expression ROW()-ROW($C$2) functions as a counter and must
contain the the address of the first cell, in which the formula is entered.

In C1 the formula is:

=INDEX($A$2:$A$11,IF(ROW()-ROW($C$1)<$B$2,1,MATCH(ROW()-ROW($C$1)+1,
SUMIF(OFFSET($B$2,,,ROW(INDIRECT("1:"&ROWS($B$2:$B$11)))),"<>")+1)+1))

and remember to commit the formula with <Shift><Ctrl><Enter>
(While pressing <Shift> and <Ctrl> press <Enter>)

Leo Heuser
 
D

Daly

Leo

thank you ,it works now because I didn't enter it as an array formula.

it's wonderful ,thanks for your great help.
 
L

Leo Heuser

Daly said:
Leo

thank you ,it works now because I didn't enter it as an array formula.

it's wonderful ,thanks for your great help.
Click to expand...
You're welcome, Daly.
Thanks for your feedback :)

Leo Heuser
 
Top