adding numbers based on two columns

[padmanabham]

Hi,

Formula needed as per below inputs
Sheet 1
[TABLE="width: 151"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]To[/TD]
[/TR]
[TR]
[TD]435190[/TD]
[TD]435200[/TD]
[/TR]
[TR]
[TD]416401[/TD]
[TD]416409[/TD]
[/TR]
[TR]
[TD]437932[/TD]
[TD]437960[/TD]
[/TR]
[TR]
[TD]435328[/TD]
[TD]435365[/TD]
[/TR]
[TR]
[TD]438001[/TD]
[TD]438046[/TD]
[/TR]
[TR]
[TD]435254[/TD]
[TD]435278[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 - in A1 should come output as per below
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]435190[/TD]
[/TR]
[TR]
[TD="align: right"]435191[/TD]
[/TR]
[TR]
[TD="align: right"]435192[/TD]
[/TR]
[TR]
[TD="align: right"]435193[/TD]
[/TR]
[TR]
[TD="align: right"]435194[/TD]
[/TR]
[TR]
[TD="align: right"]435195[/TD]
[/TR]
[TR]
[TD="align: right"]435196[/TD]
[/TR]
[TR]
[TD="align: right"]435197[/TD]
[/TR]
[TR]
[TD="align: right"]435198[/TD]
[/TR]
[TR]
[TD="align: right"]435199[/TD]
[/TR]
[TR]
[TD="align: right"]435200[/TD]
[/TR]
[TR]
[TD="align: right"]416401[/TD]
[/TR]
[TR]
[TD="align: right"]416402[/TD]
[/TR]
[TR]
[TD="align: right"]416403[/TD]
[/TR]
[TR]
[TD="align: right"]416404[/TD]
[/TR]
[TR]
[TD="align: right"]416405[/TD]
[/TR]
[TR]
[TD="align: right"]416406[/TD]
[/TR]
[TR]
[TD="align: right"]416407[/TD]
[/TR]
[TR]
[TD="align: right"]416408[/TD]
[/TR]
[TR]
[TD="align: right"]416409[/TD]
[/TR]
[TR]
[TD="align: right"]437932[/TD]
[/TR]
[TR]
[TD="align: right"]437933
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

 

[XOR LX]

Hi,

In Sheet2 cell D1, used to count the expected number of returns:

=SUMPRODUCT(1+Sheet1!B$1:B$6-Sheet1!$A$1:$A$6)

Then, defined in Name Manager (Formulas tab):

Name: Arry1
Refers to: =MMULT(0+(ROW(Sheet1!E$1:E$6)>=TRANSPOSE(ROW(Sheet1!E$1:E$6))),1+Sheet1!E$1:E$6-Sheet1!$A$1:$A$6)

After which, array formula** in Sheet2 cell A1:

=IF(ROWS(A$1:A1)>$D$1,"",INDEX(Sheet1!$A$1:$A$6,MATCH(TRUE,Arry1>=ROWS(A$1:A1),0))+IFERROR(1+ROWS(A$1:A1)-LOOKUP(ROWS(A$1:A1),1+Arry1),ROWS(A$1:A1))-1)

and copied down until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[padmanabham]

Hi,

In Sheet2 cell D1, used to count the expected number of returns:

=SUMPRODUCT(1+Sheet1!B$1:B$6-Sheet1!$A$1:$A$6)

Then, defined in Name Manager (Formulas tab):

Name: Arry1
Refers to: =MMULT(0+(ROW(Sheet1!E$1:E$6)>=TRANSPOSE(ROW(Sheet1!E$1:E$6))),1+Sheet1!E$1:E$6-Sheet1!$A$1:$A$6)

After which, array formula** in Sheet2 cell A1:

=IF(ROWS(A$1:A1)>$D$1,"",INDEX(Sheet1!$A$1:$A$6,MATCH(TRUE,Arry1>=ROWS(A$1:A1),0))+IFERROR(1+ROWS(A$1:A1)-LOOKUP(ROWS(A$1:A1),1+Arry1),ROWS(A$1:A1))-1)

and copied down until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

I have tried the formula, but it showing #N/A ,
"Name: Arry1
Refers to: =MMULT(0+(ROW(Sheet1!E$1:E$6)>=TRANSPOSE(ROW(Sheet1!E$1:E$6))),1+Sheet1!E$1:E$6-Sheet1!$A$1:$A$6)"

We have refer E column , or we have to use B column

Regards

 

[XOR LX]

I have tried the formula, but it showing #N/A ,
"Name: Arry1
Refers to: =MMULT(0+(ROW(Sheet1!E$1:E$6)>=TRANSPOSE(ROW(Sheet1!E$1:E$6))),1+Sheet1!E$1:E$6-Sheet1!$A$1:$A$6)"

That's not a formula to be entered into the worksheet; it is a defined name, to be entered in Name Manager, as described in my original post, which I suggest you re-read more carefully.

Regards

 

[DanteAmor]

If you can sort your ranges by column A, you could use the following pair of formulas on Sheet2

Sheet2
[TABLE="class: grid, width: 850"]
<tbody>[TR]
[TD]A1[/TD]
[TD]=Sheet1!A2[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]=IFERROR(IF(A1<INDEX(Sheet1!$B$1:$B$7, MATCH(A1,Sheet1!$A$1:$A$7,1) ),A1+1,IF(A1=INDEX(Sheet1!$B$1:$B$7,MATCH(A1,Sheet1!$A$1:$A$7,1)),INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$A$1:$A$7,1) + 1), INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$B$1:$B$7,0) + 1) )),"End")[/TD]
[/TR]
</tbody>[/TABLE]


Sheet1
Numbers ordered from lowest to highest in column A

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >From</td><td >To</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">416401</td><td style="text-align:right; ">416409</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">435190</td><td style="text-align:right; ">435200</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">435254</td><td style="text-align:right; ">435278</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">435328</td><td style="text-align:right; ">435365</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">437932</td><td style="text-align:right; ">437960</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">438001</td><td style="text-align:right; ">438046</td></tr></table>

 

[DanteAmor]

Edit:

The editor ate part of the formula, here again:

Sheet2 Cell A2

=IFERROR(IF(A1 < INDEX(Sheet1!$B$1:$B$7, MATCH(A1,Sheet1!$A$1:$A$7,1) ),A1+1,IF(A1=INDEX(Sheet1!$B$1:$B$7,MATCH(A1,Sheet1!$A$1:$A$7,1)),INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$A$1:$A$7,1) + 1), INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$B$1:$B$7,0) + 1))),"End")<index(sheet1!$b$1:$b$7, match(a1,sheet1!$a$1:$a$7,1)="" ),a1+1,if(a1="INDEX(Sheet1!$B$1:$B$7,MATCH(A1,Sheet1!$A$1:$A$7,1)),INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$A$1:$A$7,1)" +="" 1),="" index(sheet1!$a$1:$a$7,match(a1,sheet1!$b$1:$b$7,0)="" 1)="" )),"end")<="" html=""></index(sheet1!$b$1:$b$7,>

 

[padmanabham]

Edit:

The editor ate part of the formula, here again:

Sheet2 Cell A2

=IFERROR(IF(A1 < INDEX(Sheet1!$B$1:$B$7, MATCH(A1,Sheet1!$A$1:$A$7,1) ),A1+1,IF(A1=INDEX(Sheet1!$B$1:$B$7,MATCH(A1,Sheet1!$A$1:$A$7,1)),INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$A$1:$A$7,1) + 1), INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$B$1:$B$7,0) + 1))),"End")<index(sheet1!$b$1:$b$7, match(a1,sheet1!$a$1:$a$7,1)="" ),a1+1,if(a1="INDEX(Sheet1!$B$1:$B$7,MATCH(A1,Sheet1!$A$1:$A$7,1)),INDEX(Sheet1!$A$1:$A$7,MATCH(A1,Sheet1!$A$1:$A$7,1)" +="" 1),="" index(sheet1!$a$1:$a$7,match(a1,sheet1!$b$1:$b$7,0)="" 1)="" )),"end")<="" html=""></index(sheet1!$b$1:$b$7,>

DanteAmor - Thank you , its working fine.

XOR LX - Your formula also very interest too, i have followed your steps(Define Manager - New - Formula pasted there as Arry1) , but still not worked for me .

it would be great if you can send us the sample file to p.padmanabham@gmail.com. It will help as without sorting the sheet1 data , i will get the retults.

Again thanks to both of you

Regards

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[XOR LX]

And you followed the instructions at the foot of my original post on how to enter an array formula correctly?

Regards