Help needed with an interesting count problem...

[Anonymous]

Hello experts,

I'm currently developing an application where I need to do a count. I'm currently using a match function but I need to find out if there is a better way to resolve this.

My main table looks like this:

Now I need to count the ID by matching the items in the Item column. The items I need to match are in another file like this:

The final result I want should be like this:

so to explain this, I need go through the main table and find all the distinct ID for item a2-p. Then I need to go through the main table again and count all the distinct ID for either item a2-p or u9-x. then I need to go through the main table again and count ID for either item a2-p or u9-x or a1-y and so on and so forth

for example here I've listed only 4 items but in reality I have a list of over 3000 items I need to go through. at the moment I'm suing somehting like this to do the count

if( match(ID,$(item), ID, null()) as CountID

where the value of $(item) changes with every iteration. For first iteration it's 'a2-p'. for second it's 'a2-p', 'u9-x' etc. once this is completed then I do the count on CountID.

Anyone has any thought on this approach? how can I do this for over 3000 items? Will match work for that many items? is ther a better approach? Can I use applymap or some other method to solve this more efficiently?

Thanks

I'm looking forward to your replies.