Retrieving value from another sheet based on column

Hello guys,

I've been working on a macro (with the help from this forum) that condenses data from multiple ranges to a new sheet. I'm almost done with the macro but I'm stuck on this one final part.

I've attached an example but I'll explain the scenario.

The raw datasets are in ranges that are 3 columns wide.
the macro so far lists all the unique instances in the first sheet into one range.
What I'm having trouble with is grabbing the counts from the ranges in sheet 1 to the condensed range.

Can anyone point me in the right direction??

Also, if anyone wants the macro I have so far, I would be glad to share it.