Complex matrix count and rollup

[pcdj]

I am attempting to solve a counting problem with the data in the attached spreadsheet.

Without getting in to too much detail as to why I wish to do this, the problem I have is like this:

Each user may has one or many rows of data. Each user will never have two identical make/region/rating combinations.

The matrix to the right of the first four columns puts a '1' where the combination of data matches that for a particular user.

Each user's '1's in the matrix are rolled up to the very first row that the user appears in. Eg. User 1 has a '1' under the Audi, AM, 2 box for row 4, and a '1' under the BMW, EU, 3 box for row 5 - however both '1's appear in row 4 only...

...row 5, and any subsequent rows for that user (if there were any more), will simply contain '0's.

This will eventually be extended to many hundreds of users and perhaps 10 manufacturers (but always keeping the three regions and the five ratings).

Can someone suggest a programmatic way of calculating the zeros and ones? Replacing the example data with formulae or VB functions?

Thanks!

[shg]

Unmerge the cells in the first two rows and duplicate the data. Then in E4 and copy across and down,

=($B4=E$1) * ($C4=E$2) * ($D4=E$3) * 1

(Part of) the results looks like this:

Please Login or Register  to view this content.

[Ron Coderre]

With your posted structure...
Try this:

Please Login or Register  to view this content.

Otherwise, if you UNmerge the heading cells and enter values (like shg suggested):

Please Login or Register  to view this content.

Either way, copy the formula across and down through AH12.

Does that help?

[pcdj]

Thank you both, that certainly looks like what I'm after. I hadn't even considered unmerging the cells to simplify the problem; it's obvious now!

I shall have a try at attempting to understand what is happening in these formulae and extending them to the additional manufacturers, as mentioned in my original post.

Thanks again.

[pcdj]

Could I perhaps add the following extra caveat...

If I added a 7th user to the provided example, with the data BMW, AM, 3 and BMW, AM 2 - how would it be possible to now roll these up horizontally in the matrix and only count the maximum rating?

A point of clarification also - the matrix is essentially a list of boolean values with just a 1 or 0 if the combination is present. Ron's first solution summed the number of instances of each combination, however I've remediated this.

[Ron Coderre]

OK...I added 2 more user combinations in rows 13 and 14

The below formulas calculate the maximum rating for a User/Make/Region.

Please Login or Register  to view this content.

Whichever scenario you use, copy E4 across and down through AH4.

(I attached a sample workbook with both scenarios)

Does that help?