adding numbers based on two columns

padmanabham

New Member
Joined
Dec 19, 2014
Messages
16
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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).
 
Upvote 0
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



 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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,>
 
Last edited:
Upvote 0
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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
And you followed the instructions at the foot of my original post on how to enter an array formula correctly?

Regards
 
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top