Forum Discussion
apply titles as a new column
Hi everyone, I've been trying to work with a dataset that I uploaded through a pdf and while cleaning it I've become stuck on trying to get certain headers for groups to be applied, if what I'm sayin...
=BYROW(B1:B22,LAMBDA(row,IF(ISNUMBER(SEARCH("Staff",row)),INDEX($B$1:row,LARGE(IF(ISNUMBER(SEARCH("Group",$B$1:row)),ROW($B$1:row)),1)),"")))This formula returns the intended result in my sheet.
=IF(ISNUMBER(SEARCH("Staff",B4)),INDEX($B$1:B1,LARGE(IF(ISNUMBER(SEARCH("Group",$B$1:B1)),ROW($B$1:B1)),1)),"")With e.g. Excel 2013 you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.