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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Month[/TD]
[TD]School1[/TD]
[TD]School2[/TD]
[TD]School3[/TD]
[TD]School4[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]100[/TD]
[TD]125[/TD]
[TD]125[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]125[/TD]
[TD]100[/TD]
[TD]75[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]75[/TD]
[TD]150[/TD]
[TD]100[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]125[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]90[/TD]
[TD]125[/TD]
[TD]100[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]75[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]125[/TD]
[TD]125[/TD]
[TD]125[/TD]
[TD]125[/TD]
[/TR]
</tbody>[/TABLE]

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

Sheet2:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Student ID[/TD]
[TD]School[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]1234[/TD]
[TD]School3[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]1235[/TD]
[TD]school2[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]1236[/TD]
[TD]School1[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]1237[/TD]
[TD]School4[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Student ID[/TD]
[TD]School1[/TD]
[TD]School2[/TD]
[TD]School3[/TD]
[TD]School4[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]1235[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]1236[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]1237[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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].



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Student ID[/TD]
[TD]School1[/TD]
[TD]School2[/TD]
[TD]School3[/TD]
[TD]School4[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]1234[/TD]
[TD][/TD]
[TD][/TD]
[TD]425[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]1235[/TD]
[TD][/TD]
[TD]525[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]1236[/TD]
[TD]315[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]1237[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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