Comparing Two Collections: Looping Through one column to get Counts in power Apps

Question

There Two collections 

Collection 1st Master List of SiteID named as    "SiteIDCollectionList" - Consists of one column named as "SiteID" - Hence Consists Unique "SiteID" entries   Collection 2nd named as  ListsDetilsOfSiteIDCollection - Consists of two column named as "SiteID" & "Status" & "ListID" - Consists of duplicate Site Entries of each SiteID as per each list of that Site , "SiteID" is kind of lookup from SiteCollectionList but column type is not lookup its Single line text filed.

So want to show All "SiteID"  Count from 1st SiteIDCollectionList Collection  of each "SiteID" by comparing from 2nd collection where SiteID = SiteID & Status =Pending i.e Loop on SiteCollectionList and compare it with 2nd Collection ListsDetilsOfSiteIDCollection and and compare with SiteID = SiteID & Status =Pending and show Each SiteID count whom status is Pending.

This comparsion SiteID = SiteID & Status =Pending  ( Here 1st SiteID is from 1st collection which is comparing from another 2nd collection SiteID and Status.) 

 

Just  in brief to explain :

Collection1:

SiteID

Site001

Site002

Site003

Site005

Site889

Site778

Collection2:  This collection may have multiple entries of same siteID 

SiteID        Status

Site001       Pending

Site001       Pending

Site002       Rejected        

Site003       Pending

Site003       Pending

Site005       Pending

Site889       Rejected

Site889       Pending

Site889       Rejected

Site778       Pending

Desired Output i want:

Gallery with two column 1st is All SiteID and 2nd column its respective Count whose status

SiteID       Total Pending

Site001       2

Site002       0        

Site003       2

Site003       2

Site005       1

Site889       1

Site778       1