Conversion of two large spreadsheets into one large matrix

I have two large excel spreadsheets, each with hundreds of tabs, and each tab has approximately 7000 "Part number" lines in it.

MANY of the part numbers are duplicated across multiple tabs and it is estimated that there are only 30k to 50k unique part numbers

Data task create a matrix in a single worksheet, with every unique part number.
- Unique part number on spreadsheet rows, with the product title, group name and subgroup name.
- Headers named after each unique tab.
- Y/N Entries as to whether that part number appears in that tab name.

Attached screenshot shows a small portion of the data in its current raw form.
Attached spreadsheet "EXAMPLE DATA' shows the desired outcome.

Finished spreadsheet will have more than 300 header names, named after each tab.