Grouping data from two tables (different structure) based on common criteria

[artie note]

Hello all,
I'm new to the forum, and I think I have an interesting case:

The attached file shows what I'm trying to do - I'd like to fill in the data in the top left corner table (blue-shaded) with the data combined from the two other tables. Think of the Greek letters as names that are grouped into the groups named {A,B,C,D,E} (the names have no significance, it's just an example).

I was able to do so using an array formula and an extra column (yellow shaded) as you can see in B2 - however, this solution doesn't suite my case because the actual tables will be coming from automated processes and cannot be easily manually altered.

I tried inserting the array formula into the sumproduct formula - see B3 - but for some reason this doesn't behave the same as when the array is outside the formula. Does anyone know a reason for this and a workaround? I should also mention that I came across a user defined function solution - this too is not so suitable for me right now.

Thanks

[David A Coop]

You need to make one small adjustment to your SUMPRODUCT formula! Plus some changes to the relative addresses

=SUMPRODUCT(--($A2=$F$13:$F$21)*(B$13:B$21))

The double minus is a method of coercion for Boolean Logic. There are several methods of achieving this, but most people seem to use the "--".

[artie note]

Thanks, however I'm not sure how this solves my problem - the formula you have written already works fine in cell B2 of the example worksheet, without "--". I'm trying to get rid of the range F13:F21 by inserting the array formula that creates that range into the sumproduct - as demonstrated in B3.
I tried adding "--" into the formula in B3, as such:

=SUMPRODUCT(--($A3=INDEX($J$2:$J$13,MATCH($A$13:$A$21,$I$2:$I$13,0)))*($B$13:$B$21))

And this did not change results. What am I missing?

[sktneer]

I think if you don't want to create a helper column, you can achieve this with the help of a VBA code given below

Please Login or Register  to view this content.

Please see the attached sheet and click the "Run The Code" button to get the desired values as per your reqirement.

[artie note]

Thanks sktneer, especially for the effort. That does seem to have the characteristics needed to fill in the requested table.

As I mentioned in the original post however, I already have a VBA-based solution (a user defined function that loops trough the input tables) but I'm trying to avoid such a solution (some future users of this workbook are not very excel-literate and I do not wan't to tackle macro-enabling issues of all sorts - I know there are solutions for that but I preffer a straight forward solution if I can find one).

What bothers me the most is that the sumproduct evaluates the array when it is in a separate column but not when it is put into the formula - I don't know what the difference is? I tried using names - no luck.

[David A Coop]

You need to make one small adjustment to your SUMPRODUCT formula! Plus some changes to the relative addresses

=SUMPRODUCT(--($A2=$F$13:$F$21)*(B$13:B$21))

The double minus is a method of coercion for Boolean Logic. There are several methods of achieving this, but most people seem to use the "--".

Sorry Artie Note,

I can't think of a way to avoid the "helper column". SUMPRODUCT is an array formula which requires all the arguments to cover the same rows so that an array is formed, and it adds them all up. By introducing the second array where you are assigning the A,B C values, SUMPRODUCT gets confused.

I think you are correct, your SUMPRODUCT formula did work without the double unary, probably because you multiplied the B13 to B21 range. All it needed was to fix the Absolute References.

I understand your reluctance to have an XLSM workbook, and cope with enabling macros etc., but unless someone far smarter than me comes up with an answer, you might have to put up with the Helper Column (even if you have to hide it).

DAC


Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
If we have been of assistance, please let us know. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[artie note]

Thank you all - I think I got it right!

Here's my solution, no extra ("helper") column and no code. The references are to "dynamic" name ranges so that the size of the input tables doesn't matter.

Should anyone care to scrutinize this solution / change it to be more effective - I'd appreciate it a lot!

[David A Coop]

Thank you all - I think I got it right!

Here's my solution, no extra ("helper") column and no code. The references are to "dynamic" name ranges so that the size of the input tables doesn't matter.

Should anyone care to scrutinize this solution / change it to be more effective - I'd appreciate it a lot!

That really is clever!!!! I will study this one further.

[sktneer]

With slight variation in your lookup table (sorting the names alphabetically), you can use simple lookup formula to get the desired result.
To sort as per the requirement, select J4 then Right Click-->Sort-->A->Z and then use the formula given below in cell B3 and then drag this across and down.

Please Login or Register  to view this content.

Sometimes organizing sheet data helps to get the desired result even with a simple formula.

Pls find the attachment.

[artie note]

Thanks sktneer. I didn't realize that.
It only makes it more annoying to know that the lookup formula works in this setting, and the index and match method, which is generally superior to lookup (at least to the best of my knowledge) doesn't.