3

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

Sheet2 currently looks like this.

1ST DATE 1ST POS ARTIST SONG 01/03/70 01/10/70
Diana Ross & The Supremes Someday We'll Be Together
Jackson 5 I Want You Back
James Brown Ain't It Funky Now (Part 1)
Jr. Walker & The All Stars These Eyes
Gladys Knight And The Pips Friendship Train
Marvin Gaye & Tammi Terrell What You Gave Me
Johnnie Taylor Love Bones

I'm looking for a single dynamic array formula for Sheet2 cell E2 (the cell under the date 01/03/70).

This formula should take the artist and song values in that row and date value in that column, find the row on Sheet1 with those values, and return the corresponding THIS WEEK value.

It should then spill those returned values down all rows and across all columns starting at Sheet2 cell E2.

Based on above example of Sheet1, Sheet2 should end up looking like this.

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

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.

Improve this question
4
  • I apologize for causing everyone to work way harder than they should. I have modified my question to better explain things (hopefully). Commented Jun 5, 2024 at 15:39
  • Oh GB, you needed only the last two columns? LOL! Commented Jun 5, 2024 at 15:41
  • 1
    Exactly! After I saw everyone trying to populate all of the columns, I knew more visuals were needed. Commented Jun 5, 2024 at 15:47
  • please try now i have updated in answers as well as in the link! Commented Jun 5, 2024 at 15:54

4 Answers 4

Reset to default
2

Try using the following formula, using LAMBDA() helper function MAKEARRAY() the following will generate the whole array as is mentioned in your OP:

enter image description here

• Formula used in cell C1 of Sheet2

=LET(
     _Data, Sheet1!A2:D15,
     _ArtistSong, CHOOSECOLS(_Data,2,3),
     _Uniq, UNIQUE(_ArtistSong),
     _Date, TAKE(_Data,,1),
     _Output, MAKEARRAY(ROWS(_Uniq),COLUMNS(DROP(C1:F1,,2)), LAMBDA(r,c,
     TOROW(FILTER(TAKE(_Data,,-1), (INDEX(_Uniq,r,1)=INDEX(_ArtistSong,,1))*
     (INDEX(_Uniq,r,2)=INDEX(_ArtistSong,,2))*(INDEX(DROP(C1:F1,,2),c)=_Date),"")))),
     HSTACK(_Uniq, _Output))

As per new update by OP:

=LET(
     α, C2:D8,
     δ, E1:F1,
     MAKEARRAY(ROWS(α),COLUMNS(δ),LAMBDA(r,c,
     FILTER(Sheet1!D2:D15,(INDEX(α,r,1)=Sheet1!B2:B15)*
     (INDEX(α,r,2)=Sheet1!C2:C15)*(INDEX(δ,c)=Sheet1!A2:A15),""))))
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thank you for the single-formula solution. When I place the formula anywhere in Sheet2 range A1:D43021, it causes a circular reference error. On Sheet2, if I already have the header set up in row 1, how can the formula be edited to return the data only when placed in Sheet2 C2?
@GBMedusa well you will need to remove the headers. Let me update the same!
@GBMedusa just remove this part which is at the end: VSTACK(HSTACK(Sheet1!B1:C1,_UniqD), and one of the last brackets.
Remove all of the headers or just the ones in C1:F1?
@GBMedusa see i have updated, just copy that
|
2

Summarize Weekly Top List

  • You could use the following e.g. in Sheet2!A1:
=LET(data,Sheet1!A2:D43021,dcol,1,acol,2,scol,3,rcol,4,
        headers,{"1ST DATE";"1ST POS";"ARTIST";"SONG"},
    di,IF(data="","",data),d,FILTER(di,CHOOSECOLS(di,dcol)<>""),
    sd,CHOOSECOLS(d,dcol),sa,CHOOSECOLS(d,acol),
    ss,CHOOSECOLS(d,scol),sr,CHOOSECOLS(d,rcol),
    artist_song,UNIQUE(HSTACK(sa,ss)),
    au,TAKE(artist_song,,1),su,DROP(artist_song,,1),
    du,TOROW(UNIQUE(sd)),
    h,HSTACK(TOROW(headers),du),
    f_date,MAP(au,su,LAMBDA(a,s,XLOOKUP(1,(sa=a)*(ss=s),sd))),
    f_pos,MAP(au,su,LAMBDA(a,s,XLOOKUP(1,(sa=a)*(ss=s)*(sr=1),sd,""))),
    f_pos_count,MAP(au,su,LAMBDA(a,s,
        IFERROR(ROWS(FILTER(sd,(sa=a)*(ss=s)*(sr=1))),))),
    ranks,MAKEARRAY(ROWS(artist_song),COLUMNS(du),LAMBDA(r,c,
        XLOOKUP(1,(sd=INDEX(du,c))*(sa=INDEX(au,r))*(ss=INDEX(su,r)),sr,""))),
    r,VSTACK(h,HSTACK(f_date,f_pos,HSTACK(artist_song,ranks))),
    r)
  • Replace the bottom-most r with any other variable to see what it holds.
  • Using MAKEARRAY is probably the most efficient way to solve your problem i.e. focus on ranks.

Source

enter image description here

Result

enter image description here

Improve this answer

3 Comments

Heavy Solution.
It s really not that heavy if dates are dynamic. I use reduce instead of makearray simply because it is easier to write. So used to intelisense that I just cannot get used to writing complicated formulas completely off hand on my own.
@rachel with Heavy i mean to say nice solution. Not the weight. In my mother tongue Heavy means that actually hebby , nice.
1

Another really lazy solution that I would advise you to use with caution:

=CHOOSECOLS(WRAPROWS(TOROW(SORT(A2:D15,{2,3},1,FALSE)),8),{2,3,4,8})

enter image description here

First, sort the data by song and artist name, so that row1 is 1/3/1970, row2 is 1/10/1970...

enter image description here

Then use WRAPROWS and TOROW to reshape the table:

enter image description here

Finally, use CHOOSECOLS to keep the relevant columns.

Edited:

Since this formula relies on the assumption that each (artist + song) has two rows, one for 1/3/1970, another for 1/10/1970, if that is not true, e.g if one of (artist + song) has three dates, or one only one date, this whole formula will breakdown.

And I saw other answers handle this properly, so I decided to the take some time to write a formula to handle this too.

That is, if one of the artist has three dates, it will still work:

=LET(
    data, A2:D16,
    sorted_dates, SORT(UNIQUE(INDEX(data, , 1), FALSE, FALSE), 1, 1),
    artists, INDEX(data, , 2),
    songs, INDEX(data, , 3),
    unique_artists_and_songs, UNIQUE(HSTACK(artists, songs), FALSE, FALSE),
    rowNum, ROWS(unique_artists_and_songs),
    colNum, ROWS(sorted_dates),
    REDUCE(
        HSTACK("Artist", "Song", TOROW(sorted_dates)),
        SEQUENCE(rowNum, 1, 1, 1),
        LAMBDA(a, i,
            LET(
                artist, INDEX(unique_artists_and_songs, i, 1),
                song, INDEX(unique_artists_and_songs, i, 2),
                filtered, FILTER(data, (artists = artist) * (songs = song), ""),
                dates, INDEX(filtered, , 1),
                ranks, INDEX(filtered, , 4),
                sorted_ranks, MAP(sorted_dates, LAMBDA(d, XLOOKUP(d, dates, ranks, "", 0))),
                result, HSTACK(artist, song, TOROW(sorted_ranks)),
                VSTACK(a, result)
            )
        )
    )
)

enter image description here

Improve this answer

2 Comments

Not tested. But nice. However have you checked for the Odd Even!
@MayukhBhattacharya, I think the main problem with TOROW + WRAPROWS is that it relies on the assumption that each (artist + song) has two rows, one for 1/3/1970, another for 1/10/1970, if that is not true, e.g if one of (artist + song) has three dates, or only one date, not only will this whole formula breakdown, but it would be rather difficult to eyeball the problem too.
1

Another option:

=LET(header, A1:D1,
     data,   A2:D15,
     date,TAKE(data,,1),     artist,INDEX(data,,2),     song,INDEX(data,,3),     notation,DROP(data,,3),     unq,UNIQUE(HSTACK(artist,song)),
     unqdate,TOROW(UNIQUE(date)),     unqartist,TAKE(unq,,1),     unqsong,DROP(unq,,1),
VSTACK(HSTACK("1ST "&{"DATE","POS"},
              CHOOSECOLS(header,2,3),
              unqdate),
       HSTACK(XLOOKUP(unqartist&unqsong,artist&song,date),
              XLOOKUP(unqartist&unqsong&1,artist&song&notation,date,""),
              unq,
              WRAPCOLS(TOCOL(BYCOL(--REPT(notation,date&artist&song=TOROW(unqdate&unqartist&unqsong,,1)),LAMBDA(b,IFERROR(TOCOL(b,2),"")))),ROWS(unq),1))))

Where you change your header and data to your needs, the rest adapts to that ranges.

I tried using self explaining names inside LET.

Basically it looks up the first date of the artist & song combination, next it looks up the first number 1 notation date, if applicable. Then the unique artist/song combinations are stacked to that, followed by it's pivoted notations Using BYCOL/REPT.

Improve this answer

1 Comment

Nice use case of BYCOL()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.