List to Matrix

[giotto60]

I am looking for a solution for an apparently simple but I believe quite complex problem that has been bugging us in the office for days. I know it would be easy to solve this with VBA, but I would like to find a formula-only solution, I think there should be one.

It's not easy to explain so please check the following file:

Example.xlsx

The link will die in 7 days so if any of you can re-post it in another site I'd be grateful. I have a list of colors and a list of items. One item may have more than one color, and of course the same color can appear in many items. Now, from here I want to go to a matrix where I have all the colors listed in the first column and all the colors in the first row. I then want to populate this matrix with the number of items that have the color in that row AND the color in that column.

We have 3 reasonably skilled Excel users in the office and we don't even know where to start. Actually two of us think this is impossible, I personally believe it can be done with matrix formulas but no clue even how to get started.

I am looking for a formula-only solution (I know this would be easy in VBA) and hopefully without any auxiliary columns or data rearranging. Just one formula, to be written in the matrix and dragged, that gives the solution. I actually am starting to doubt that this is even possible, but on the other hand it looks so simple...

If a formula solution is not possible, the next step would be a pivot table, we haven't managed to find how to do that with a pivot table either.

Any ideas?

 

[hatstand]

I can't see you file as I use a work computer. But from what you have written. You will need to use a custom function to allow the coloured cells to be used in regular calculations.

 

[p45cal]

In your file, the cell G4 contains the result 5. (Black/Black)
When I filter the list for Black I get:
Black A
Black B
Black C
Black C
Black E

There are two instances of Black C.
Can you confirm that the result should be 5 and not 4?

 

[giotto60]

p45cal,

First of all thanks for taking the time to look at my problem.

Answering your question, I missed that, yes, there is a duplicate entry with the same color and the same item... it doesn't matter either way, it can be 4 or 5, I can process the list beforehand to avoid this.

 

[Laurent C]

There are 3 colors associated with item B. Is it an issue?

 

[Laurent C]

Try this one:
{=SUM((COUNTIFS(colors,G$12,items,items)>0)
*(COUNTIFS(colors,$F13,items,items)>0)
*(MATCH(items,items,0)=(ROW(items)-1)))}

It will only count distinct occurrences of items (that is what the MATCH part is for).

 

[giotto60]

Laurent C,

You, sir, are pure awesome. I am speechless and will never dare to think that I am a "reasonably skilled" Excel user again, compared to this, what I am is an Excel pre-beginner.

Thanks a lot, it's perfect!