Using Calc Create A Set of Iterations from a List

[T_H]

Using Calc Create A Set of Iterations from a List

I have a given list of 3 items and would like to add 1more item to it and then iterate through with the combinations of the 3 + 1 , with only the “1” pushing the existing list around to accommodate its new position. I am not looking to try to solve for all combinations. I tried HLOOKUP/VLOOKUP but I’m stumbling on combining the two. I also tried some IF statements working off the Tag List/Positions (Column A and Row 9).

I feel that the lookup is the way to go to basically work off an x,y grid to then place the data from B4. I’ve attached an example sheet of what I’m thinking below. For this small data set I can do it manually let alone just setting everything =B4 etc as needed. But with a growing data set I’d like to have it flexible and able to adapt.

Any guidance will be greatly appreciated, thanks!

My desired outcomes is to take my starting list

1a
2a
3a

and add a single new value

1b

Is to have 1b placed into the 1st,2nd,3rd,4th,nth positions of a 1st,2nd,3rd,4th,nth list.

Example-Iterate New Item Into List.ods (15.1 KB)

[JohnSUN]

The logic of the formula is not difficult to understand. Look not at the entire resulting table at once, but only at one row.

If the column number matches the row number, then a New Tag is placed in the cell. To the left of this cell - the same value from the Tag List, which corresponds to the row number, and to the right - the next value from the same list.

In other words, based on the current row and current column, you can calculate which of the values ​​should be in the cell. In order for the formula to work anywhere on the sheet, it will have to be adjusted based on the top left cell of the results table. If we conditionally call it StartCell, then the formula will be as follows:

=IF((ROW()-ROW(StartCell))=(COLUMN()-COLUMN(StartCell));
       New_Tag; 
       IF((ROW()-ROW(StartCell))<(COLUMN()-COLUMN(StartCell));
         OFFSET(First_Cell_In_Tag_List ;ROW()-ROW(StartCell);0); 
         OFFSET(First_Cell_In_Tag_List;ROW()-ROW(StartCell)-1;0)))

or in relation to your demo data -

=IF((ROW()-ROW($C$16))=(COLUMN()-COLUMN($C$16));$B$4;IF((ROW()-ROW($C$16))<(COLUMN()-COLUMN($C$16));OFFSET($B$10;ROW()-ROW($C$16);0);OFFSET($B$10;ROW()-ROW($C$16)-1;0)))

Since the second IF() is very cumbersome, and its meaning is reduced only to calculating the offset of 0 or 1, then the expression can be greatly simplified to

OFFSET($B$10; ROW()-ROW(StartCell)-((ROW()-ROW(StartCell))>(COLUMN()-COLUMN(StartCell)));0)

in a general form.

The final formula for the sample data is
=IF((ROW()-ROW($C$16))=(COLUMN()-COLUMN($C$16));$B$4;OFFSET($B$10;ROW()-ROW($C$16)-((ROW()-ROW($C$16))>(COLUMN()-COLUMN($C$16)));0))

[JohnSUN]

By the way, the formula can be written even shorter

=IF(ROW($A1)-COLUMN(A$1);OFFSET($B$10;ROW($A1)-(ROW($A1)>COLUMN(A$1))-1;0);$B$4)

But I would not recommend doing this - it is very difficult to understand what and how it does.

[T_H]

Thanks for the help on this, I appreciate the explanation of details allowing me to follow along with the process as you worked through it. I’ll definitely stick with the elongated formula in the event I need to retrace steps with a similar function in the future.