Count set of comma separated values within another paired columns

[Gulya]

Hi Experts,

I would like to count set of comma separated values from another table.

For example, AF17=2,0,2 so i want to check/count this paired/set value within another paired/joined columns of 3,4,7=AD17.

The below mentioned formula is working fine if there is a single value in column AD & AF, but i need a formula to get the results based on a comma separated range & pairs please.

Please Login or Register  to view this content.

I have attached the sample file. Thanks in advance.

[sandy666]

with Power Query

Please Login or Register  to view this content.

[Gulya]

Thanks Sandy for the support,
It would be great if I could get a formula please. Tried to amend the below mentioned formula based on the above requirement, but got stuck

Please Login or Register  to view this content.

[sandy666]

i don't deal with formulas, sorry
you'll need to wait for someone else
have a nice day

[Gulya]

Thanks Sandy,
Hope there will be some other feedbacks from Excel gurus.

[sandy666]

there is a chance

[JEC.]

Pull this one down from AG6

Please Login or Register  to view this content.

[Gulya]

Thanks JEC for the support, but the result is not the one that I was looking for.

To be more precise, for example, AD15=3,4,7 and AF15=2,2,2 so my requirement is that first; join the entire columns of (3,4,7) then count 2,2,2 pairs within that joined columns. And the result of AH15=3, because there are only 3 sets of 2,2,2 within joined columns 3,4,7(b,c,f).

I have mentioned the expected results in column AH. Can you please check the attached file.

Thanks in advance.

[Gulya]

Can someone help with the above please, tried with the below formula but getting ab error.

Any advice or solution would be greatly appreciated. Thanks

Please Login or Register  to view this content.

[JEC.]

Goodmorning, try this one

Please Login or Register  to view this content.