Inconsistent Mapping Requirements Within Data Set

[sta357]

Hi, I need to find a way to simply map data from a table to a series of reports. Normally, this would be accomplishable through a simple SUMIFS, but the requirement is a bit more complicated. I want to create two reports. One for Business A and one for Business B.

Attached is a file containing a data set, mapping table and two example reports.

If a VLOOKUP or INDEX/MATCH are used to look up the Business into the data tab using Group, Group 1 will only associate with Business A and not Business B, since the lookup will find and pull the first value.

Note that the data set and mapping tables that are being used in reality are very large and change often. The above is an example. The issue being highlighted occurs several times within the mapping table / report requirements. As such, I'm trying to keep the file size limited and the model dynamic, so a solution which duplicates the data within the data set and requires entering a static index number next to each row is not ideal.

Are there any thoughts on a simple system to map the data into the reports given these requirements?

Thanks!

[quekbc]

This was fun.

Business A, N6: =SUM(SUMIFS(D$4:D$15,$B$4:$B$15,IF($J$4:$J$7=$M$4,$K$4:$K$7),$C$4:$C$15,$M6))

Business B, U6: =SUM(SUMIFS(D$4:D$15,$B$4:$B$15,IF($J$4:$J$7=$T$4,$K$4:$K$7),$C$4:$C$15,$T6))

NOTE: This is an array formula, as such, please type in the formula and press CTRL SHIFT ENTER.

Or see attached.

[samba_ravi]

Nice one Quekbc
or try
below array formula
=SUM(SUMIFS(D$4:D$15,$B$4:$B$15,$K$4:$K$7,$C$4:$C$15,$M6)*($J$4:$J$7=$M$4))

=SUM(SUMIFS(D$4:D$15,$B$4:$B$15,$K$4:$K$7,$C$4:$C$15,$M6)*($J$4:$J$7=$M$4))

try this and copy across

[cyiangou]

This was also fun. I read many a post requesting data manipulation that seems better suited to a database than a spreadsheet (you can fulfill your requirement in MSAccess in under 5 minutes with no code), but there are also cases where you just need some SQL power for parts of your largely spreadsheet application. And this is an Excel forum after all.

SqlSplit.xlsm

I used your question as my test case, but I intend to keep this code in my own toolbox.

• Uses no spreadsheet overhead; should be minimal on memory in general (vba array usage will still be a limitation)
• No need to copy formulas further down than is necessary to cater for unknown record counts
• Uses no formulas, and minimal vba data processing
• It should be very fast for large data (I haven't tested)
• Only the first 2 procedures are specifically related to your requirement, the others are generic and you can use them for other similar tasks.
• 'Tables' are referred to in SQL queries as range names, which in turn simply refer to the cell at the top-left cell of the sheet data.

In the attached example, the range name 'Data_Table' refers to Sheet1!B4. When executing, the code resolves the single cell into a Range using the CurrentRegion property. See CurrentRegion help for rules about how it autodetects its extents.

[quekbc]

Perfect. Can't forget the snowflakes. Kids will throw massive tantrums.

[quekbc]

Cyiangou. When I run it, it doesn't seem to be producing outputs for Snowflakes.

[cyiangou]

Odd, it was. But now it's not. Stand by...

[cyiangou]

Meh. It was leaving off the last column too. Corrected version:

SqlSplit.xlsm

[cyiangou]

Haha! Thanks for the heads up. Edit: Oh, and the rep!

[sta357]

Extremely helpful. Thanks!