Auto Inserting Column based on V-Look up results

Unsure if this is even possible but thought I would ask as a Google search is coming up blank.

Basically I want to be able to auto insert it a column if the V-Look up data returns more than one value.

eg
SHEET 1
Column 1 - Column 2 - Column 3
PARTNO1 Competitor 1 EAF2330
PARTNO1 Competitor 1 EAF2882
PARTNO1 Competitor 1 EAF3002
PARTNO2 Competitor 1 EAF1550
PARTNO3 Competitor 2 EAF0446
PARTNO3 Competitor 2 EAF8990
PARTNO4 Competitor 1 EAF8992
PARTNO5 Competitor 4 EAF8555



SHEET 2 - Destination - INITIAL STRUCTURE
Competitor1 Competitor2 Competitor3 Competitor4
PARTNO1
PARTNO2
PARTNO3
PARTNO4
PARTNO5



SHEET 2(Destination)

Competitor1 Competitor1 Competitor1 Competitor2 Competitor2 Competitor3 Competitor4
PARTNO1 EAF2330 EAF2882 EAF3002
PARTNO2 EAF1550
PARTNO3 EAF0446 EAF8990
PARTNO4 EAF8992
PARTNO5 EAF8555


Using a VLOOKUP/VBA etc. how can I get to the finished file?
Basically I want it so that it automatically adds Columns based on the number of cross references and should a cross reference be removed i.e. EAF3002, it would then automatically remove the extra column as it's not needed.
Also, lets say an extra Cross reference for 'Competitor 4' was added against PARTNO5, it would auto add a column for Competitor 4 as there would now be 2 cross references applied to Competitor 4.

Is this even possible? In the past I have used an INDEX as an Array Formula to show values horizontally but what I'm after now is something similar, but something dynamic that will recognise the Competitor and auto add/remove columns as necessary.


(I tried adding the Excel file but unable to attach on browser)