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




--
thank you & best regards

"Daly" skrev i en meddelelse
...
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




--
thank you & best regards


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)=TRANSPO SE(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.

"Leo Heuser" skrev i en meddelelse
...
"Daly" skrev i en meddelelse
...
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




--
thank you & best regards


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)=TRANSPO SE(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.


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

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


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

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
--
thank you & best regards


"Leo Heuser" wrote:

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


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

"Daly" skrev i en meddelelse
...
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
--
thank you & best regards

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

Leo

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

it's wonderful ,thanks for your great help.

--
thank you & best regards


"Daly" wrote:

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
--
thank you & best regards


"Leo Heuser" wrote:

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


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

"Daly" skrev i en meddelelse
...
Leo

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

it's wonderful ,thanks for your great help.

--
thank you & best regards


You're welcome, Daly.
Thanks for your feedback :-)

Leo Heuser