Return List of Values with Exceptions for Special Grouping

nau2002

Board Regular
Joined
Dec 19, 2016
Messages
97
Hello,

I have a spreadsheet I need to modify in a way I'm not sure is possible. I currently have formulas in place to return all 'Projects' (beginning in row A6) that match a selected 'Group' (as indicated in cell B2) and include the actual spending against each 'Project' for the selected time period (as indicated in cell B3). In the example below, since 'Cats' is selected for '2016 Q2', all projects matching that group are returned along with the 'Actual' cost for that period.

A
B
C
D
2
Group
Cats
Data Validation
3
Quarter
2016 Q2
Cats
4
Dogs
5
Project
Actual
Birds
6
Project 1
35,455
Fish
7
Project 2
64,545
2016 Q1
8
2016 Q2
9
2016 Q3
10
2016 Q4
11
2017 Q1
12
2017 Q2
13

<tbody>
</tbody>

Here is an example of the formula in cell A6:
=IFERROR(INDEX('Source Data'!$D$4:$D$19,SMALL(IF(ISNUMBER(MATCH('Source Data'!$B$4:$B$19,$B$2,0)),ROW('Source Data'!$B$4:$B$19)-ROW('Source Data'!$4:$4)+1),ROWS($A$6:$A6))),"")

The challenge is, I have a new requirement where within the group 'Fish' (and only within the group 'Fish'), there are some projects (but not all 'Fish' projects) that should be combined with other projects from other groups (e.g. Cat). For these such projects, the dollars should just be added to the other project. Here are the values that should be combined:

'Fish' Projects to be combined:
Actual Group
Combination Group
PROJECT 1A
Project 1
(Cats)
Project 4A
Project 4
(Dogs)

<tbody>
</tbody>

So, Project 1A should have it's $'s added to Project 1 (which appears under 'Cats'). In the example above, if Project 1A had actuals costs of $20k for the selected period, the result for Project 1 should be $55,455.

Project 1A should not be listed anywhere in the results (whether the user selects 'Cats' or 'Fish' which is the original group. Can my formulas in Column A be modified to accommodate for such a requirement? Note that the list of projects requiring combination is fairly short and static. I can easily maintain them in a table on the spreadsheet or, given it's just a few of them, put them directly in the formula by name if there's a way to do that.

Thanks in advance! Sorry to be so long-winded on this request!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,129,447
Messages
5,636,326
Members
416,912
Latest member
danluk12

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
Top