Forum Discussion
Excel outline question
I have an Excel spreadsheet covering many columns and rows, mostly of text rather than numbers. Is there a way I can outline my entries so that what begins as Sport Team Division Netball ...
Perhaps..
=LET(myData,A1:C9,
division, TAKE(myData,,-1),
sport, BYROW(INDEX(myData,,1), LAMBDA(r, IFERROR(IF(OFFSET(r,-1,0)<>r,r,""),r))),
team, BYROW(INDEX(myData,,2), LAMBDA(r, IFERROR(IF(OFFSET(r,-1,0)<>r,r,""),r))),
HSTACK(team,sport,division))
- Thanks, but I couldn't make it work on my sheet. Generated an error around the value of r.
Try this other variation..
=LET(sport, A1:A9, team, B1:B9, division,C1:C9, HSTACK( IF(COUNTIFS(OFFSET(INDEX(team, 1), 0, 0, SEQUENCE(COUNTA(team))), team)=1,team,""), IF(COUNTIFS(OFFSET(INDEX(sport, 1), 0, 0, SEQUENCE(COUNTA(sport))), sport)=1,sport,""), division))
