matching multiple vehicles with appropriate parts

[leadfeet]

In Sheet 3 I have layed out a template (parts list #1 on sheet 2) of what I am looking for and I have did the first two part numbers for an example. I have Sheet 4 set up the same way, but those are the part numbers in list #2 on sheet 2. I want to keep them separate.

Issues

The part numbers in both lists (1 &2) on sheet two have a letter before the (.), for example HB103E.590 however, the part numbers on Sheet 1 have an (x) replacing those letters (HB103x.590). The letter represents the compound of the brake pad, the compound list is displayed to the right of each part number in a separate column.
I want to transfer the application (vehicles) to the template pages in sheets 3 & 4. The problem is, there are several vehicles per part number, so there are duplicate part numbers in sheet 1. I want single part numbers with all the vehicles that part number fits.
There will be part numbers that on Sheet 2 on both list #1 and list #1 that may not have applications listed on sheet 1, but I want to keep those part numbers in tact, they will just be missing applications.
I plan on adding both the description and applications together, but I want them separate for now so I can add phrases like (***Product Fits: applications…….). I can do that later once I get the applications with the appropriate single part numbers. I hope I have explained this alright, it is a complicated thing, for me anyways. I appreciate all the help, so thank you in advance!