Another awkward dataset. Should we re-arrange the data or just find a way to calculate what we need?

(get my Excel file)

Awkward Data

A nice normalized dataset would have 4 columns: Category, Month, Letter, Value. But we have this:

Each month has a group of 3 columns. If we had more months this pattern would repeat.

The Question

Add values in columns C, F, I for each month/letter combination. The answers look like this:

The key to solving this is finding the pattern in the awkward data layout.

3 ‘As Is’ Solutions

Here are 3 formula solutions that work on the data as is (no rearranging).

Solution 1

=SUMIFS(OFFSET($A$3:$A$11,0,MATCH($I14,$B$2:$G$2,0)+2),OFFSET($A$3:$A$11,0,MATCH($I14,$B$2:$G$2,0)+1),J$13)

Long formula but I love OFFSET even if it’s a bit volatile.

Solution 2

=SUM(IF(($B$2:$E$2=$I19)*($C$3:$F$11=J$18),$D$3:$G$11,””))

An array (requires control shift enter).

Solution 3

=SUMPRODUCT(($B$2:$E$2=$I24)*($C$3:$F$11=J$18),$D$3:$G$11)

This is probably my favorite solution. Short and it’s a normal formula (non array).

Which formula is your favorite?

Do you have a better formula solution? (maybe using dynamic arrays?!)

Rearranging The Data

Rearrange the data so we can create a pivot. It should look like this:

Power Query

Can you rearrange this data using Power Query?

Your queries would need to adjust if the dataset grows (more months). I’d love to see if it’s possible!

Formulas

Well, that’s what we had to do in the old days so you should suffer too!

My file has my solution. It’s backwards compatible to any Excel version (no dynamic arrays).

It’s good practice to normalize using formulas. Note: formulas are in the same sheet as the data. Not a good idea but it’s a demo and it’s easier to read like this. Now we can create a simple pivot table with this data 🙂

VBA

Of course VBA could rearrange this data. But I’m too lazy to create all those loops right now!

About Me

My name is Kevin Lehrbass. I’m a data analyst from Canada.

I saw this awkward dataset just before covid crisis. Things have really changed…but there’s always been awkward data!

I play around with data to keep my mind busy and share ideas with others. I’m hoping that someone can solve this using Power Query. I gave up and went back to formulas.