The Log table has three columns of text. In A, a list of parts (widgets and flanges only). In B, parts have types. In C, they have flavors (vanilla and chocolate only).



What I want is to count the number of chocolate types of parts. Multiple instances of flange/left/chocolate only counts as one. If flange/top has both flavors, it counts as one. If flange/sideways only has vanilla, then it counts as 0.

The Metrics tab has what I want. There are 4 flange types and 6 widget types that are chocolate. However, I've had to do it indirectly. On the Parse tab, I have a list of all combinations (or is that permutations, I can never remember) of parts and types. Then I do a countifs for each of those for chocolate only on the data in the Log table. That gives me the total chocolate for each part/type.

Then on the Metrics tab, I have more countifs just counting those on the Parse tab that are greater than zero.

The problem is if a new type is added without my knowing. Say now there is a new record in the Log widget/green/chocolate. The Metrics calculation will be incorrect since that part/type isn't in the Parse table.

Can anyone think of a way to do it all directly in Metric tab and eliminate the need for the Parse tab altogether? I thought of using FREQUENCY but that requires bins to be set up. I suppose that could be done with VBA, but I'd like to see if anyone has any ideas for a formula before I go down that rabbit hole.

Attached Files

getdistinct.xlsx (15.6 KB, 15 views)

I have a solution for your question - I think?
I'll have to open your file that you included to observe.
Basically I had the same issue and solved it with nested IF's and AND statements. There may be other shorter methods but this worked for me.
To clarify, you prefer to solve via Excel and not VBA - correct?
Thanks,
Sgt Rock (Mort, like the salt)

Hi gebobs,
The formula used in the attached file is:
=SUM(IF(FREQUENCY(IF($A$2:$A$27&$C$2:$C$27=A20&C20 ,MATCH($B$2:$B$27,$B$2:$B$27,0)),IF($A$2:$A$27&$C$ 2:$C$27=A20&C20,MATCH($B$2:$B$27,$B$2:$B$27,0)))>0 ,1))
It’s an array formula, needs to be confirmed with CSE (Ctrl+Shift+Enter, not just Enter after editing the formula)
The key is the IF condition that sends the Type Column to Frequency function, but only the rows that contains Flange&Chocolate, Flange&Vanilla,Widget&Chocolate in joined columns A and C.
There is a typo error in Log sheet, Type column in cell B21, Yellow value has a trailing space, that is wrongly identifying this row as unique. I recommend using data validation lists, to avoid errors like this.
Cheers,
Catalin

Attached Files

getdistinct.xlsx (16.5 KB, 7 views)

Correct. But a VBA solution would be acceptable if nothing else presents itself. This would be a breeze in Access, but Access seems to have run its course.

Thanks. Very nice. But is there a way to just have formula in the Metrics tab? That would be preferable. The Log tab data will be imported from a database generated CSV and may be tens of thousands of lines long. If not, this may work.

This was just dummy data I drummed up for the example. As above, that data will be database generated.

Got your formula to work in the Metrics tab, Catalin!!! How do I mark as solved?

Go to the top of this page, under Thread tools you'll see an option: "Mark this thread as solved"
Glad you managed to adapt it yourself
Cheers,
Catalin