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 saying doesn't make sense I've attached two pictures, the first one is what the data looks like now, the second is what I'm trying to make it look like. Any help would be really appreciated.
This is on Excel version 2208 for Microsoft 365.
2 Replies
=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.
If I understand correctly it should be fairly simple.
Cell C5 should insert =B2 copy B@ to all the other cells in that group
Same for other groups.
