Forum Discussion

MT3's avatar
MT3
Copper Contributor
Mar 26, 2026

Filling a column with succeeding lists

Hi,

I need a list of files, with numbers starting from 001 for each box. Adding the number for one box is easy but I have thousands of them.

A box number is like "0001AA0001". The first number and the letters never change, so I have :

0001AA0001
0001AA0002
0001AA0003
Then I have to add the files numbers, like "0001AA0001/001". I already have a line for each file, but only with the box number in the forst column, repeated several times, each time for every file. 
What I haveWhat I need
0001AA00010001AA0001/001
0001AA00010001AA0001/002
0001AA00010001AA0001/003
0001AA00020001AA0002/001
0001AA00020001AA0002/002
0001AA00030001AA0003/001
0001AA00040001AA0004/001
0001AA00040001AA0004/002
0001AA00040001AA0004/003

Can you please tell me how can I add automatically the /001 and so on without having to do it for each box ? I can't manage to use a model for CTRL+E and I'm not accustomed to use the functions.

Thanks,

M.T.

excel
Formulas and Functions
Macros and VBA
Office Scripts

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 29, 2026
    = FILE.NUMBER(boxNumber)

where

FILE.NUMBER = LAMBDA(box,
    LET(
        change?, N(DROP(VSTACK("", box), -1) <> box),
        fileNum, SCAN(0, change?, LAMBDA(num, ch?, IF(ch?, 1, num+1))),
        box & "/" & TEXT(fileNum, "000")
    )
);

    If you use 365 then the Lambda function FILE.NUMBER would product the required list from the box numbers.

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 26, 2026
    =A2&"/"&TEXT(COUNTIFS($A$2:A2,A2),"000")

    The screenshot shows the formula in german Excel.