I have been wrestling with ChatGPT (and losing) trying to find an answer to this one.
This is a sample of weekly data displayed in A2:D43021 of Sheet1.
| DATE | ARTIST | SONG | THIS WEEK |
|---|---|---|---|
| 1/3/1970 | Diana Ross & The Supremes | Someday We’ll Be Together | 1 |
| 1/3/1970 | Jackson 5 | I Want You Back | 2 |
| 1/3/1970 | James Brown | Ain’t It Funky Now (Part 1) | 3 |
| 1/3/1970 | Jr. Walker & The All Stars | These Eyes | 4 |
| 1/3/1970 | Gladys Knight And The Pips | Friendship Train | 5 |
| 1/3/1970 | Marvin Gaye & Tammi Terrell | What You Gave Me | 6 |
| 1/3/1970 | Johnnie Taylor | Love Bones | 7 |
| 1/10/1970 | Jackson 5 | I Want You Back | 1 |
| 1/10/1970 | Diana Ross & The Supremes | Someday We’ll Be Together | 2 |
| 1/10/1970 | James Brown | Ain’t It Funky Now (Part 1) | 3 |
| 1/10/1970 | Jr. Walker & The All Stars | These Eyes | 4 |
| 1/10/1970 | Johnnie Taylor | Love Bones | 5 |
| 1/10/1970 | Marvin Gaye & Tammi Terrell | What You Gave Me | 6 |
| 1/10/1970 | Gladys Knight And The Pips | Friendship Train | 7 |
On Sheet2, I want to display the data like this on rows 2 to 4557.
| 1ST DATE | 1ST POS | ARTIST | SONG | 01/03/70 | 01/10/70 |
|---|---|---|---|---|---|
| Diana Ross & The Supremes | Someday We’ll Be Together | 1 | 2 | ||
| Jackson 5 | I Want You Back | 2 | 1 | ||
| James Brown | Ain’t It Funky Now (Part 1) | 3 | 3 | ||
| Jr. Walker & The All Stars | These Eyes | 4 | 4 | ||
| Gladys Knight And The Pips | Friendship Train | 5 | 7 | ||
| Marvin Gaye & Tammi Terrell | What You Gave Me | 6 | 6 | ||
| Johnnie Taylor | Love Bones | 7 | 5 |
For now, columns A & B are blank. Cells E1 to TF1 are populated with unique values from the DATE column on Sheet1. Columns C & D are populated with unique values from the ARTIST and SONG columns on Sheet1.
I have used Power Query to create a pivot table but I would like to do more calculations directly from Sheet1.
My pc is too underpowered to handle lookup formulas in each cell.
I’ve worked with versions of this formula for cell E2 but I can’t get a grip on the logic for populating both vertically and horizontally.
=LET(
data,'Sheet1'!A2:D43021,
artist, $C$2:$C$4557,
song, $D$2:$D$4557,
datehdr, $E$1:$TF$1,
BYROW(artist&song,
LAMBDA(row,
MATCH(
TRUE,
(row=INDEX(data,,2)&INDEX(data,,3))*(INDEX(datehdr,,1)=INDEX(data,,1)),
0
)
)
)
)
Any suggestions would be greatly appreciated.
Try using the following formula, using LAMBDA() helper function MAKEARRAY() the following will generate the whole array as is mentioned in your OP:
• Formula used in cell C1 of Sheet2
=LET(
_Data, Sheet1!A2:D15,
_ArtistSong, CHOOSECOLS(_Data,2,3),
_Uniq, UNIQUE(_ArtistSong),
_Date, TAKE(_Data,,1),
_UniqD, TOROW(UNIQUE(_Date)),
_Output, MAKEARRAY(ROWS(_Uniq),COLUMNS(_UniqD), LAMBDA(r,c,
TOROW(FILTER(TAKE(_Data,,-1), (INDEX(_Uniq,r,1)=INDEX(_ArtistSong,,1))*
(INDEX(_Uniq,r,2)=INDEX(_ArtistSong,,2))*(INDEX(_UniqD,c)=_Date),"")))),
VSTACK(HSTACK(Sheet1!B1:C1,_UniqD),HSTACK(_Uniq, _Output)))