My goal is to build a automatic coordinates file from a X and Y pitch for X and Y steps. Let say, X pitch = 2 (mm) Y pitch = 1 (mm) X steps = 10 and Y steps = 10

My file should like something like this

    1, 0, 0
    2, 2, 0
    3, 4, 0
    4, 6, 0
    5, 8, 0        
    6, 10, 0
    7, 12, 0
    8, 14, 0
    9, 16, 0
    10, 18, 0
    11, 0, 1
    etc 
    (till 100)

With the sequence function I managed too build to first column of numbers =SEQUENCE(L1*L2;1;1;1) L1=Xsteps L2=Ysteps

Now I am struggeling to build the X and Y column While X is repeating after every step for 10 times, Y is only incrementing every 10 times.

I would like to automate it, because in real life it's never a nice round number. But how?

CodePudding user response：

This answer is maybe not complete, but I started with row:

1     0             0
=A1 1 =MOD(B1 2,20) =IF(MOD(A2,10)=0,C1 $D$1,C1)

This creates following list:

1   0   0   10308
2   2   0
3   4   0
4   6   0
5   8   0
6   10  0
7   12  0
8   14  0
9   16  0
10  18  10308
11  0   10308

CodePudding user response：

If you have Excel 365, you can use Let with Sequence:

=LET(rows,Xsteps*Ysteps,
seq,SEQUENCE(rows,1,0),
column1,seq 1,
column2,MOD(seq,Xsteps)*Xpitch,
column3,INT(seq/Xsteps)*Ypitch,
CHOOSE({1,2,3},column1,column2,column3))

The four cells A2, B2, C2 and D2 are assigned names using the Name Manager based on the names in A1, B1, C1 and D1 respectively.