|
Excell function
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 |
Excell function
"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. |
Excell function
"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 |
Excell function
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 |
Excell function
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 |
Excell function
"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 |
Excell function
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 |
Excell function
"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 |
| All times are GMT +1. The time now is 01:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com