CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
RemcoE33
RemcoE33

Reputation: 1620

Arrayformula of sequeses

I have a list of reservations with a startDate, endDate and itemNumber. Now i want to have a list of all the dates between those dates and the itemnumber next to it. I have this working for 1 reservation, but i can't get it to work with multiple reservations in one arrayformula.

I have a sample sheet (edit rights) with my formula's and the expected outcome.

Someone here that can help me out?

Sample sheet

Upvotes: 0

Views: 45

Answers (1)

General Grievance
General Grievance

Reputation: 5033

Two Formula Version:

Item Column (Assuming K):

=ArrayFormula(TRANSPOSE(SPLIT(JOIN(,REPT(Reservation!D2:D&",",1+Reservation!C2:C-Reservation!B2:B)),",")))

or

=ArrayFormula(
    IFNA(
        HLOOKUP(
            "StartDate",
            Reservation!B:B,
            MATCH(
                FILTER(K3:K,K3:K>0),
                Reservation!D2:D,
                0
            )+1,
            0
        )+IFNA(
            ROW(K3:K)-2-MATCH(K3:K,K3:K,0)
        )
    )
)

This generates the list of Items based on the difference between the dates.

Date Column (Assuming J):

=ArrayFormula(IFNA(HLOOKUP("StartDate",Reservation!B:B,MATCH(FILTER(K3:K,K3:K>0),Reservation!D2:D,0)+1,0)+IFNA(ROW(K3:K)-2-MATCH(K3:K,K3:K,0))))

or

=ArrayFormula(
    TRANSPOSE(
        SPLIT(
            JOIN(,
                REPT(
                    Reservation!D2:D&",",
                    1+Reservation!C2:C-Reservation!B2:B
                )
            ),
            ","
        )
    )
)

Based on the Item, get its offset in the Item column (above) and add it to the Start Date for the corresponding item.

Single Formula Version:

Looks very messy, but is mostly just substituting K's into second formula above.

=ArrayFormula(
{
  IFNA(HLOOKUP("StartDate",Reservation!B:B,MATCH(TRANSPOSE(SPLIT(JOIN(,REPT(Reservation!D2:D&",",1+Reservation!C2:C-Reservation!B2:B)),",")),Reservation!D2:D,0)+1,0)+IFNA(SEQUENCE(COUNTA(TRANSPOSE(SPLIT(JOIN(,REPT(Reservation!D2:D&",",1+Reservation!C2:C-Reservation!B2:B)),","))))-MATCH(TRANSPOSE(SPLIT(JOIN(,REPT(Reservation!D2:D&",",1+Reservation!C2:C-Reservation!B2:B)),",")),TRANSPOSE(SPLIT(JOIN(,REPT(Reservation!D2:D&",",1+Reservation!C2:C-Reservation!B2:B)),",")),0))),
  TRANSPOSE(SPLIT(JOIN(,REPT(Reservation!D2:D&",",1+Reservation!C2:C-Reservation!B2:B)),","))
})

Or if you prefer:

=ArrayFormula(
{
    IFNA(
        HLOOKUP(
            "StartDate",
            Reservation!B:B,
            MATCH(
                TRANSPOSE(
                    SPLIT(
                        JOIN(,
                            REPT(
                                Reservation!D2:D&",",
                                1+Reservation!C2:C-Reservation!B2:B
                            )
                        ),
                        ","
                    )
                ),
                Reservation!D2:D,0
            )+1,
            0
        )+IFNA(
            SEQUENCE(
                COUNTA(
                    TRANSPOSE(
                        SPLIT(
                            JOIN(,
                                REPT(
                                    Reservation!D2:D&",",
                                    1+Reservation!C2:C-Reservation!B2:B
                                )
                            ),
                            ","
                        )
                    )
                )
            )-MATCH(
                TRANSPOSE(
                    SPLIT(
                        JOIN(,
                            REPT(
                                Reservation!D2:D&",",
                                1+Reservation!C2:C-Reservation!B2:B
                            )
                        ),
                        ","
                    )
                ),
                TRANSPOSE(
                    SPLIT(
                        JOIN(,
                            REPT(
                                Reservation!D2:D&",",
                                1+Reservation!C2:C-Reservation!B2:B
                            )
                        ),
                        ","
                    )
                ),
                0
            )
        )
    ),
    TRANSPOSE(
        SPLIT(
            JOIN(,
                REPT(
                    Reservation!D2:D&",",
                    1+Reservation!C2:C-Reservation!B2:B
                )
            ),
            ","
        )
    )
})

Upvotes: 3

Related Questions