use formulas or power query to add/duplicate linked rows with data based on column

Hi all,

I posted my first question a few hours ago and had answers to my days long problem so quickly, I can't even believe it :D So thank you guys so much!!

Here's another question I've wracked my brain on for days and never came up with a solution.

In the attached workbook, I need a formula or power query that works fast and doesn't interrupt workflows that takes the values from columns in the "MASTER" sheet that will be continuously updated and puts them in different columns in the "AnteraProducts" sheet. However, the rows need to line up, and currently they don't, so many of the cells from other columns from MASTER sheet will need to be duplicated into more rows based on that. The formula/query needs to look at the text before the semicolon in the "AutoTranspose" sheet and use that to compare with the "product_number" column A in the MASTER sheet. When it finds they match, it puts the rest of the needed column cells across normally. However, since for many rows there will be multiple rows on the AutoTranspose sheet where the text before the semicolon matches a single row on the Master sheet product_number, it will need to duplicate the other columns' cells down until the text before semicolon from Autotranspose matches the next row on the master sheet.

Example:

product number on master sheet:	number on AutoTranspose sheet:	output on new sheet:	text from another column on Master Sheet:	output on new sheet:
PMG1-1035	PMG1-1035;Embroidered	PMG1-1035	Bulk	Bulk
PMG1-2799	PMG1-1035;Transfer	PMG1-1035	Cello Bag	Bulk
	PMG1-1035;Silk-Screen	PMG1-1035		Bulk
	PMG1-1035;Blank	PMG1-1035		Bulk
	PMG1-2799	PMG1-2799		Cello Bag

A better example is in sheet labelled "EXPECTED RESULT" in the workbook here (sorry, it was too large to attach and i can't post links yet, so remove spaces):
johnezh-my.sharepoint . com /:x:/g/personal/m1199_auto365vip_com/Eb4gIesH7PZIkiHRp58QnI8BaOhfCuEXJMGRVbDafHyy8A?e=aEzdA2

For now Ignore the pricing columns, but everything that has a bold title in Row 1 in the AnteraProducts sheet (as well as Expected results sheet) has the same column title as one of the columns in the MASTER sheet, besides the keywords. These are the columns that need to be pulled according to the titles. However, I'll be editing these column titles, so whatever formula or query I use cant rely on the column titles to move over the data. But, they will stay in the same columns, so it can rely on column numbers like A B C etc.

The keywords will need to be pulled in the same manner as the other columns with all the duplication and etc, but from the "KeywordSplit" sheet. It also needs to get rid of any spaces inside those cells.



It needs to stay as a formula or power query because a lot of the data are formulas linked to the MASTER sheet in the same workbook. The MASTER sheet will be updated with more rows, and all the other sheets should update automatically afterwords.

It doesn't matter how many helper columns/sheets I need, but I need it to work fast and update smoothly, so that most likely rules out array formulas since as far as i've seen they take forever to update and sometimes crash my system when looking through millions of cells.

I seriously hope you guys are able to help...

Thank you so much!!