Combining 2 columns of names into one and matching other column info

[Davidm54]

Hello All,

I have power automate triggering tasks and filling in a spreadsheet when certain emails come through.

It's basically to track actions with strict timeframes. Power automate can filter out replies, and using Power query in excel, I can create a new table that extracts the relevant names from the email title.

The emails can come in with 2 names, not just one, so the columns are Name 1, Name 2, Due Date, and Reminder Date.

For tracking purposes and to work with other sheets, it would be better if the 2 Names columns were one, but still matched to the correct dates. See pic below. Table on left is how data comes through, after a clean. Table on right is how I'd like the data.

(You can ignore the blank name rows, they were earlier tests. All future emails will have name in the title. The email title will always be structured "Particular phrase for powerautomate to pick up; Name 1; Name 2")

Any suggestions?

[#2]

This solves the issue:

• An Array (CSE) Formula in cell E96:

{=IFERROR(INDEX($A$96:$A$103, SMALL(IF(ISBLANK($A$96:$A$103), "", ROW($A$96:$A$103)-MIN(ROW($A$96:$A$103))+1), ROW(A1))), IFERROR(INDEX($B$96:$B$103, SMALL(IF(ISBLANK($B$96:$B$103), "", ROW($B$96:$B$103)-MIN(ROW($B$96:$B$103))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK($B$96:$B$103)))))), ""))}

N.B. 

• Finish formula with Ctrl+Shift+Enter & fill down.
• Sort Names in Column E.
• Adjust cell references in the formula as needed.

[#3]

@Davidm54 Since you are already using Power Query, you can unpivot the two name columns and perform a few clean-up steps. Example attached.

[#4]

@Davidm54 

Like @Riny_van_Eekelen I suspect that waiting until the data is loaded into Excel in an unwanted format is a little late in the game to start making adjustments.  That said Excel 365 is evolving to became a very different programming platform from traditional spreadsheets and techniques are becoming available that will allow you either to transform the data or simply use it as it stands.

[#5]

@Peter Bartholomew , what's the reason to use formulas on the top of returned by Power Query table if you may add couple of more steps to already existing query?

[#6]

True, but the OP showed a picture of the Table but did not show the steps of the query that gave rise to it. Since a single formula proved sufficient to transform the data, I thought it to be worth showing, even if the underlying message is "If I were you, I wouldn't start from here".

[#7]

Thanks for this. I should mention that I generally share from my private account/365 account and my work Office is sometimes a different version. I may have just missed the pivot options, but I'll definitely check it out. Certainly the cleanest way.

Appreciate all who have given formulas as well.