Need VBA to find Procure value from D-Code column by multiple check

Hi Experts

I have set of data where as 3 relative sheet with similar 2 column values for criteria check.

i have raw data in sheet Procure_Data


i want find which D-code valid for procurement in column AS:AS

where else those sheet are referral for D-code mapping {DOI Citation},{NIFTY},{CBD}


Those D-code validate in sheet with G&H after slash each dealer's sheet.
i split ledger as New/Renew and Declined
Normal :G2&H2 in sheet {DOI Citation},{NIFTY},{CBD}
Most Prior:G3&H3 in sheet {DOI Citation},{NIFTY},{CBD}
Check1#
For 1 Client ID case
Consider first Most Prior list if D-Code Meet Most Prior any values then result Procure
if not met
Consider Normal Prior list if D-Code meet in Normal Piror any values then result Procure

For more than 2 or more Client ID Cases with ledger
Check2#
Consider first Most Prior list if D-Code Meet Most Prior any values then result Procure
add with Normal Prior list if D-Code meet in Normal Piror any values then result Procure

short brief
if Ledger is New/Renew 2 procure allowed if Most prior list more than 2 procure
if ledger cancelled only 1 procure allowed rest of all Non procure

its really bit complicated i am really spending alot time with manual split and frustrated

find the attachment u can understand bit.

Any Experts Help On This