SOLVED

# Excel iterate over table and list output with spilling formula

Copper Contributor

# Excel iterate over table and list output with spilling formula

Hi, i am looking for a formula that iterates byrow and presents the differences between the months like the output below. I am not fully sure how to tackle the problem with a spilling formula. Somebody any suggestions how to approach (especially the reference to the previous value and the way you should iterate the table (with map or a combination byrow / bycol).

Michiel

5 Replies
best response confirmed by MichielS115 (Copper Contributor)
Solution

# Re: Excel iterate over table and list output with spilling formula

=DROP(REDUCE("",SEQUENCE(ROWS(B2:L8)),

LAMBDA(u,v,

VSTACK(u,

TRANSPOSE(

VSTACK(

IFNA(EXPAND(INDEX(B2:B8,v),,10),INDEX(B2:B8,v)),

C1:L1,

HSTACK(

"",

DROP(

MAP(DROP(CHOOSEROWS(C2:L8,v),,1),CHOOSEROWS(C2:L8,v),

LAMBDA(aa,bb,aa-bb)),,-1)

))))))

,1)

This formula returns the intended result in my sample file if i correctly understand what should be done.

# Re: Excel iterate over table and list output with spilling formula

Nice solution with good outcomes. thans a lot
I need a bit of time to fully understand the logic behind it :grinning_face_with_sweat:

# Re: Excel iterate over table and list output with spilling formula

@MichielS115 An alternative lambda-free method (non-iterative) could be:

``````=LET(
table; A1:O7;
col_labels; DROP(TAKE(table; 1);; 1);
row_labels; TAKE(DROP(table; 1);; 1);
values;     DROP(table; 1; 1);
net_change; values - HSTACK(TAKE(values;; 1); DROP(values;; -1));
VSTACK(
HSTACK("Item"; "Date"; "Net Change");
HSTACK(
TOCOL(IF(SEQUENCE(; COLUMNS(values)); row_labels));
TOCOL(IF(SEQUENCE(ROWS(values)); col_labels));
TOCOL(net_change)
)
)
)``````

Adjust the table range reference as needed.

Also, if you prefer blanks instead of zeros for the first month, adjust the net_change variable as follows:

``````=LET(
...
net_change; IFNA(HSTACK(""; DROP(values;; 1) - DROP(values;; -1)); "");
...
)``````

See attached...

# Re: Excel iterate over table and list output with spilling formula

My preferred approach was to offset the data and subtract before unpivoting.

``````= LET(
offsetData,    HSTACK(TAKE(data, , 1), DROP(data, , -1)),
difference,    data - offsetData,
)``````

# Re: Excel iterate over table and list output with spilling formula

@Peter Bartholomew very elegant solution 🙇‍:male_sign:

1 best response

Accepted Solutions
best response confirmed by MichielS115 (Copper Contributor)
Solution

# Re: Excel iterate over table and list output with spilling formula

=DROP(REDUCE("",SEQUENCE(ROWS(B2:L8)),

LAMBDA(u,v,

VSTACK(u,

TRANSPOSE(

VSTACK(

IFNA(EXPAND(INDEX(B2:B8,v),,10),INDEX(B2:B8,v)),

C1:L1,

HSTACK(

"",

DROP(

MAP(DROP(CHOOSEROWS(C2:L8,v),,1),CHOOSEROWS(C2:L8,v),

LAMBDA(aa,bb,aa-bb)),,-1)

))))))

,1)

This formula returns the intended result in my sample file if i correctly understand what should be done.