elsone31
08-08-2015, 11:28 AM
Using Excel 2013
I have a workbook that has a tab for New data, Old Cumulative and working.
I want to subtract the data on the New Data and the Cumulative tabs, that have matching headers. I was able to write formula(located in the workbook the Working sheet starting in cell F2) to do that. The problem is I can not come up with a way to get the data that is ONLY on the new data tab to populate.
I have tried using an IF statement in conjunction with match, posted below. They work separately, but not together.
So far so good Finds new data
=IF(MATCH(F1,'Old Cumulative'!F1:L1,0),INDEX('New Data'!$F$2:$IV$4000,MATCH(Working!$E2,'New Data'!$E$2:$E$4000,0),MATCH(Working!F$1,'New Data'!$F$1:$IV$1,0))-INDEX('Old Cumulative'!$F$2:$IV$4000,MATCH(Working!$E2,'Old Cumulative'!$E$2:$E$4000,0),MATCH(Working!F$1,'Old Cumulative'!$F$1:$IV$1,0)))
2nd part Returns data if no new data was added to TEK
INDEX('New Data'!$F$2:$IV$4000,MATCH(Working!$E2,'New Data'!$E$2:$E$4000,0),MATCH(Working!F$1,'New Data'!$F$1:$IV$1,0))
I would appreciate any assistance with the formula as well as ideas to get it into a macro.
Thanks in advance for any assistance.
I have a workbook that has a tab for New data, Old Cumulative and working.
I want to subtract the data on the New Data and the Cumulative tabs, that have matching headers. I was able to write formula(located in the workbook the Working sheet starting in cell F2) to do that. The problem is I can not come up with a way to get the data that is ONLY on the new data tab to populate.
I have tried using an IF statement in conjunction with match, posted below. They work separately, but not together.
So far so good Finds new data
=IF(MATCH(F1,'Old Cumulative'!F1:L1,0),INDEX('New Data'!$F$2:$IV$4000,MATCH(Working!$E2,'New Data'!$E$2:$E$4000,0),MATCH(Working!F$1,'New Data'!$F$1:$IV$1,0))-INDEX('Old Cumulative'!$F$2:$IV$4000,MATCH(Working!$E2,'Old Cumulative'!$E$2:$E$4000,0),MATCH(Working!F$1,'Old Cumulative'!$F$1:$IV$1,0)))
2nd part Returns data if no new data was added to TEK
INDEX('New Data'!$F$2:$IV$4000,MATCH(Working!$E2,'New Data'!$E$2:$E$4000,0),MATCH(Working!F$1,'New Data'!$F$1:$IV$1,0))
I would appreciate any assistance with the formula as well as ideas to get it into a macro.
Thanks in advance for any assistance.