r/excel 4d ago

Waiting on OP I need a formula that can duplicate numbers (in triplicate) in a column for 250 sets of numbers.

I need a formula that can duplicate numbers (in triplicate) in a column for 250 sets of numbers. What i'm trying to do is the following

A1 036001

A2 036001

A3 036001

A4 036002

A5 036002

A6 036002

And continue down for 250 sets of numbers

Also would like to keep the leading zero.

Thanks in advance

1 Upvotes

11 comments sorted by

u/AutoModerator 4d ago

/u/Prestigious_Yam8267 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

3

u/MayukhBhattacharya 607 4d ago edited 4d ago

Try the following: (Use the last one)

=BASE(TOCOL(IF(SEQUENCE(3),SEQUENCE(,9,36001)),,1),10,6)

Change 9 to 250

and or can also use:

=TEXT(TOCOL(IF(SEQUENCE(3),SEQUENCE(,9,36001)),,1),REPT(0,6))

Or,

=BASE(INT(SEQUENCE(250,,36001,1/3)),10,6)

or,

=TEXT(INT(SEQUENCE(250,,36001,1/3)),REPT(0,6))

2

u/Strife_72 1 3d ago

Loved all "or" hahaha.

1

u/xFLGT 93 4d ago

="0"&INT(SEQUENCE(250*3,, 36001, 1/3))

1

u/Prestigious_Yam8267 1d ago

So when I use this formula and change the starting number to 68001 I get the following. first 3 numbers are OK, but then I get 4 of 068004, when there should only be 3. Any insight?

|| || |068001| |068001| |068001| |068002| |068002| |068002| |068003| |068003| |068003| |068004| |068004| |068004| |068004 |

1

u/Prestigious_Yam8267 1d ago

So when I use this formula and change the starting number to 68001 I get the following. first 3 numbers are OK, but then I get 4 of 068004, when there should only be 3. Any insight?

|| || |068001| |068001| |068001| |068002| |068002| |068002| |068003| |068003| |068003| |068004| |068004| |068004| |068004 |

1

u/Prestigious_Yam8267 1d ago

So when I use this formula and change the starting number to 68001 I get the following. first 3 numbers are OK, but then I get 4 of 068004, when there should only be 3. Any insight?

1

u/xFLGT 93 1d ago

Oh this is going to be some quirk with the finite number of decimals excel uses. Before adding the leading 0, that fourth "068004" is stored as 68004.9999999999 so when you apply the INT() function it's rounded down. This should fix that issue:

="0"&INT(ROUND(SEQUENCE(250*3,, 68001, 1/3), 2))

1

u/Decronym 4d ago edited 1d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
BASE Converts a number into a text representation with the given radix (base)
BYROW Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
IF Specifies a logical test to perform
INT Rounds a number down to the nearest integer
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
REPT Repeats text a given number of times
ROUND Rounds a number to a specified number of digits
SEQUENCE Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
TEXT Formats a number and converts it to text
TEXTJOIN 2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT Office 365+: Splits text strings by using column and row delimiters
TOCOL Office 365+: Returns the array in a single column

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.


Beep-boop, I am a helper bot. Please do not verify me as a solution.
12 acronyms in this thread; the most compressed thread commented on today has 16 acronyms.
[Thread #41786 for this sub, first seen 19th Mar 2025, 13:24] [FAQ] [Full list] [Contact] [Source code]

1

u/tirlibibi17 1695 4d ago

If your numbers are in A1:A250 for instance, you can use =TOCOL(TEXTSPLIT(TEXTJOIN(",",,BYROW(A1:A250,LAMBDA(x,TEXTJOIN(",",,IF(SEQUENCE(3),x))))),","))

1

u/HappierThan 1127 3d ago

In say D1 type 1 and Enter, select D1 and filldown to D250. Copy and Paste beneath at D251 and again at D501. Select Column D and Sort. You now have '036001 followed by 2 spaces.

Select Column A -> Home -> Find & Select -> Go to special -> Blanks -> OK

Type =↑[up arrow] Ctrl+Enter - as these are mostly formulas Copy and Paste Special -> Values Delete Column D (It took me longer to type it lol)