Forum Discussion

Davidm54's avatar
Davidm54
Brass Contributor
Apr 08, 2021

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

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?

excel
Formulas and Functions

6 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 08, 2021

    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.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 08, 2021

      PeterBartholomew1 , 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?

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Apr 08, 2021
        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".
    • Davidm54's avatar
      Davidm54
      Brass Contributor
      Apr 09, 2021
      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.
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Apr 08, 2021

    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.

     

     

Resources