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 fil...
excel
Formulas and Functions
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