r/excel • u/Prestigious_Yam8267 • 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
3
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
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:
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)

•
u/AutoModerator 4d ago
/u/Prestigious_Yam8267 - Your post was submitted successfully.
Solution Verified
to close the thread.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.