Compare two sheets and sum a range of values

[sharan250]

Hi,

I have two sheets in a workbook. Sheet1 includes information on average monthly pocket money of students from different schools.
Sheet1:

Month	School1	School2	School3	School4
Jan	100	125	125	100
Feb	125	100	75	125
Mar	75	150	100	75
Apr	100	100	125	100
May	90	125	100	125
Jun	100	150	75	100
Jul	125	125	125	125

<tbody>
</tbody>

Sheet2 includes information about Students studing in which school and the month.

Sheet2:

Month	Student ID	School
Jan	1234	School3
Mar	1235	school2
May	1236	School1
June	1237	School4

<tbody>
</tbody>

Somewhere in sheet2, I have a value say in cell E1. Lets say E1 has a value 4.

Now, I want information from sheet2 to be matched with Sheet1 and in case of match copy it on sheet3 as

Step1:

Month	Student ID	School1	School2	School3	School4
Jan	1234
Feb
Mar	1235
Apr
May	1236
Jun	1237
Jul

<tbody>
</tbody>

Later as step2, based on Student ID and corresponding school, we need to identify their corresponding month and value of E1 (for this example we have considered it as 4).
Case1:
For Student ID: 1234
Month: Jan,
School: School3
E1: 4

On sheet1 after mapping Jan and corresponding School3 value for month Jan, I need to add pocket money for next 4 months. i.e. pocket money of Jan, Feb, Mar, Apr will be added (i.e. 125, 75, 100, 125) and the sum (425) value will be inserted in Sheet3[row Jan; Column School3].

Case2:

ID: 1235
Month: Mar
School: School2
E1: 4

On sheet1 after mapping Mar and corresponding School2 value for month Mar, I need to add pocket money for next 4 months. i.e. pocket money of Mar, Apr, May, Jun will be added (i.e. 150, 100, 125, 150) and the sum (525) value will be inserted in Sheet3[row Mar; Column School2].

Case3:
ID: 1236
Month: May
School: School1
E1: 4

On sheet1 after mapping May and corresponding School1 value for month Mar, I need to add pocket money for next 4 months. i.e. pocket money of May, Jun, Jul, Aug. As we dont have August in Sheet1, so we add only May, Jun, Jul and he sum (i.e. 90 + 100 + 125= 315) value will be inserted in Sheet3[row May; Column School1].

Case4:
ID: 1237
Month: Jun
School: School4
E1: 4

On sheet1 after mapping Jun and corresponding School1 value for month Jun, I need to add pocket money for next 4 months. i.e. pocket money of Jun, Jun, Aug, Sep. As we dont have Aug and Sep in Sheet1, so we add only Jun, Jul and the sum (i.e. 100 + 125= 225) value will be inserted in Sheet3[row Jun; Column School4].

Month	Student ID	School1	School2	School3	School4
Jan	1234			425
Feb
Mar	1235		525
Apr
May	1236	315
Jun	1237				225
Jul

<tbody>
</tbody>

Thanks in advance for all your help.

Cheers

 

[zaeious]

Step 1

On Sheet3 Column B Row 2, under 'Student ID', put in this formula: =IFERROR(VLOOKUP($A2,Sheet2!$A$1:$B$5,2,FALSE),"")

Step 2

Copy the formula from Column B Row 2 to the Last Row

Step 3

On Column C Row 2, under 'School1', put in this formula: =IFERROR(IF(EXACT(VLOOKUP($B2,Sheet2!$B$2:$C$5,2,FALSE),C$1),IFERROR(INDEX(Sheet1!B$2:B$8,MATCH($A2,Sheet1!$A$2:$A$8,0)),0)+IFERROR(INDEX(Sheet1!B$2:B$8,MATCH($A2,Sheet1!$A$2:$A$8,0)+1),0)+IFERROR(INDEX(Sheet1!B$2:B$8,MATCH($A2,Sheet1!$A$2:$A$8,0)+2),0)+IFERROR(INDEX(Sheet1!B$2:B$8,MATCH($A2,Sheet1!$A$2:$A$8,0)+3),0),""),"")

Step 4

Copy this formula across Column C to F, under ' School4', and copy the entire formula down from Row 2 to the Last Row

This is an interim solution, please ensure both tables at Sheet1 and Sheet2 to start at Column A Row 1, also ensure that no cells are being dragged or deleted from the tables, else the formula will return REF# error. Although I have used =iferror() function to keep the results clean, it's best to refrain from removing or shifting any cells.

I do have a stronger formula which uses =indirect() and range names to remove that issue but let's keep it simple for now XD

Hopes this helps!

 

[sharan250]

Hi Zaeious,

Thanks for your help. This indeed helps.

I am looking for a macro, which will be robust and can help to counter dynamic value. e.g. if the number of rows changes, column changes and value of E1. So that I dont have to care for any error.

Cheers

I do have a stronger formula which uses =indirect() and range names to remove that issue but let's keep it simple for now XD

Hopes this helps!

 

[zaeious]

Hi Zaeious,

Thanks for your help. This indeed helps.

I am looking for a macro, which will be robust and can help to counter dynamic value. e.g. if the number of rows changes, column changes and value of E1. So that I dont have to care for any error.

Cheers

You're welcome, I am not well-versed in macros and VB Applications so as of now I can only provide this solution.

Change this =IFERROR(VLOOKUP($A2,Sheet2!$A$1:$B$5,2,FALSE),"")

to this =IFERROR(VLOOKUP(INDIRECT("$A"&ROW()),Sheet2!$A:$B,2,FALSE),"")

and

change this
=IFERROR(IF(EXACT(VLOOKUP($B2,Sheet2!$B$2:$C$5,2,FALSE),C$1),IFERROR(INDEX(Sheet1!B$2:B$8,MATCH($A2,Sheet1!$A$2:$A$8,0)) ,0)+IFERROR(INDEX(Sheet1!B$2:B$8,MATCH($A2,Sheet1!$A$2:$A$8,0)+1),0)+IFERROR(INDEX(Sheet1!B$2:B$8,MATCH($A2,Sheet1!$A$2: $A$8,0)+2),0)+IFERROR(INDEX(Sheet1!B$2:B$8,MATCH($A2,Sheet1!$A$2:$A$8,0)+3),0),""),"")

to this
=IFERROR(IF(EXACT(VLOOKUP(INDIRECT("$B"&ROW()),Sheet2!$B:$C,2,FALSE),INDIRECT(CHAR(64+COLUMN())&1)),IFERROR(INDEX(INDIRECT("Sheet1!"&CHAR(63+COLUMN())&":"&CHAR(63+COLUMN())),MATCH(INDIRECT("$A"&ROW()),Sheet1!$A:$A,0)),0)+IFERROR(INDEX(INDIRECT("Sheet1!"&CHAR(63+COLUMN())&":"&CHAR(63+COLUMN())),MATCH(INDIRECT("$A"&ROW()),Sheet1!$A:$A,0)+1),0)+IFERROR(INDEX(INDIRECT("Sheet1!"&CHAR(63+COLUMN())&":"&CHAR(63+COLUMN())),MATCH(INDIRECT("$A"&ROW()),Sheet1!$A:$A,0)+2),0)+IFERROR(INDEX(INDIRECT("Sheet1!"&CHAR(63+COLUMN())&":"&CHAR(63+COLUMN())),MATCH(INDIRECT("$A"&ROW()),Sheet1!$A:$A,0)+3),0),""),"")

This will allow u to add new months and schools with indefinite rows but is limited up to Column Z