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 | Panthers | u/8 |
| Netball | Panthers | u/9 |
| Netball | Panthers | u/10 |
| Soccer | Panthers | u/8 |
| Soccer | Panthers | u/9 |
| Soccer | Panthers | u/10 |
can be shown as
| Team | Sport | Division |
| Panthers | Netball | u/8 |
| u/9 | ||
| u/10 | ||
| Soccer | u/8 | |
| u/9 | ||
| u/10 |
I have used a simplified example. My spreadsheet is an excel export query from SharePoint. It is important not to break the query data connection.
6 Replies
Another 365 formula; this time comparing every cell against the one above and returning changes as a 2D array.
The biggest hassle is switching columns!
= LET( priorData, DROP(VSTACK(header, myData),-1), changes, IF(myData<>priorData, myData, ""), CHOOSECOLS(changes, 2, 1, 3) )A formula can do this but may be more trouble than it's worth. Attached is a Pivot solution.
- Thanks, Patrick. I will have a look at the pivot solution and see if I can still do my printouts as required. My audience doesn't have direct access to the document.
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))
