The times when you had to put numbers in sequence in Excel manually are long gone. In modern Excel, you can make a simple number series in a flash with the Auto Fill feature. If you have a more specific task in mind, then use the SEQUENCE function, which is specially designed for this purpose. Continue reading
Comments page 3. Total comments: 437
Hello,
I want to create a reference number formula in an excel cell in a payment voucher such that when I save one payment voucher and I close and open the payment voucher again, It will increment itself.
Thank you.
Hi! Unfortunately, without seeing your data, it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.
Hello
I'm attempting to link two different spreadsheets, one being a view/ work-log, the other is an entry-log. The work-logs only purpose is to calculate the remaining dollar amount and percentages. The entry-log is an ongoing form that totals around 50 individual sheets, which means every new entry will differ. The entry-log will only use 7-12 cells that reflect on the work-log in sequential order. The problem I'm having is how to add a traspose fx that will work in sequence with the two forms to prevent me from having to link them manually for each sheet?
Is it possible to add a sequence with this formula below without removing the transpose fx?
=TRANSPOSE('[Weatherization - Joshen RFP.xlsx]JOSHEN '!$C$14:$C$17)
Hello! It is very difficult to understand a formula that contains unique references to your data, which I don't have. Unfortunately, I can't understand what you want to do.
hi,
I have three columns : employee, salary (yearly), startdate. I want to generate a provisional monthly cashflow table for the next 3 years with three columns : employee, payment (monthly), paymentdate.
I know how to this using SEQUENCE for one employee but is there a way to generate it at once for several employees ? In other words, this would be the same as generating sequence lines one below the previous.
Thanks
Hi! You can create only one sequence using the SEQUENCE formula.
Hi,
I have CSV data that has been loaded into Excel using the common method under the Data / From Text menu, into a separate worksheet labelled "Raw Data". I want to paste this data into another worksheet from a start position identified as "1". The simple formula used to achieve the "1" starting position is =IF(CE$4=BJ695,1,""), where CE$4 is time in DD/MM/YYYY hh:mm:ss format and BJ695 is one of a series of cells with the same time format. The time is in sequential order in column BJ. The formula identified places a "1" as the starting position in column "CE". In the adjacent column "CD" the data from the worksheet "Raw Data" should start. I have tried Index Match formulae to no avail and am losing my mind over this. Is there a simple way of completing this task without introducing many more helping columns, as the worksheet is already large? I would greatly appreciate any help provided.
Many thanks... Damian.
Hi! Dates that you load to Excel from a CSV file, are usually not written as dates, but as text. Check this and use these guidelines: How to convert text to date in Excel. I hope it’ll be helpful.
Can I use the sequence function if I want to repeat number 12 times then incrementing 1 until I reached 24 it goes back to number 1? Thanks
Hi , I want to apply a function on a generated sequence. Say there are 5 numbers {1,2,3,4,5} , I want harmonic sum of each the numbers in the sequence and then I want cumulative sum of the results. How will it be possible ?
Hi! Please clarify your specific problem or provide additional information to understand what you need. Maybe this article will be helpful: How to do a running total in Excel (Cumulative Sum formula).
I don't think this can be done with a sequence function. Write down 24 numbers and copy them as many times as necessary.
Hi ,
Seek your guidance how shall i proceed built the sequence formula for below scenario in excel template
1.scenario 1
13/9/ABC/01
13/9/ABC/02
13/9/ABC/03
13/9/ABCa/04
13/9/ABC/05
13/9 > referring to DD/MM base on today/weekday case ID receive
ABC - defaulted
01 - refer to sequence number for each entry of the row
2. Scenario 2 sequence format
202309120001
202309120002
202309120003
202309120004
"20230912" - referring to current request date receive for the data entry
"0001", 002,003 - referring to sequence number of request received for the day
if the subsequent date no request, then will no case ID generate
Assume case receive on 20230915, then the case ID number will be 2020309150001.....follow by 202309150002...etc
Thank you.
Hi! Use the TEXT function to write dates and numbers in the desired format.
=TEXT(A1,"mm.dd")&"."&"ABC/"&TEXT(SEQUENCE(10,1,1,1),"00")
thank you so much on your useful tips.
The serial number working perfectly for Date 9 Oct 2023. However, when is new date eg: 10 Oct 2023, 11 Oct 2023, the sequence number is not reset start with "0001". Expected format shall be 10/10 ABC/0001, 10/10 ABC/0002.....etc if there is new date 11 Oct 2023 then shall be 11/11 ABC/0001, 11/11 ABC/0002....etc. Can this be done with formula?
9-Oct-23 09/10 ABC/0001
9-Oct-23 09/10 ABC/0002
9-Oct-23 09/10 ABC/0003
9-Oct-23 09/10 ABC/0004
9-Oct-23 09/10 ABC/0005
10-Oct-23 10/10 ABC/0006
10-Oct-23 10/10 ABC/0007
12-Oct-23 12/10 ABC/0008
13-Oct-23 13/10 ABC/0009
Thank you.
Hi! Based on your second description, it is not about creating a sequence. You need to create code for each new record. You can use the COUNTIF formula for dates to determine the ordinal number of the record within each day. Unfortunately, without seeing your data, it is impossible to offer you a formula.
I need to create a sequence that consists of two columns, the first being Serial No., which contains sequence 1 to 100,000. Columns. 2 is having a code that will have a format based on the serial number value from column 1, i.e., 1 to 1000, code value will be 1, column 1001-2000 column 2 value is 2, which means after each 1000 the code value will change. Can you please help find a simple way to do that?
Hi! You can find the formula for the first column in the article above. For the second column, you can use the rounding to thousands formula:
=ROUNDUP(A1/1000,0)
Or you can use the sequence formula, as has been described many times in the comments to this article:
=CEILING(SEQUENCE(100000,1,1,1)/1000,1)
How wil autogenerate responses for 200 respondents, already have for 55 responses in excel
i have a set of responses from 55 respondents, but i need like 200 ,how will i autogenerate the remaining responses .
Is it possible
300/7/2023
301/7/2023
302/7/2023
303/7/2023
Hi! Create a sequence as described in the article above and add a text string using the & operator.
=SEQUENCE(10,1,300,1)&"/7/2023"
Hello, I have these names in A1:A8 and need to enumerate them on C1:C8, excluding the ones that say treasury. The sequence also depends on the year selected in B1:B8
A B
1 Other property 2023
2 Passenger vehicle 2023
3 Non-passenger vehicle 2023
4 Treasury 2023
5 Other Property 2023
6 Non-passenger vehicle 2024
7 Treasury 2024
8 Non-passenger vehicle 2024
Expected sequence in C
1 01
2 02
3 03
4
5 04
6 01
7
8 02
Hi! The expected sequence you described is not very clear to me. Also note that you will be able to use the year if it is written in a separate column as a number.
Hello, Unfortunately I'm stuck with Excel 2016 and unable to use the SEQUENCE function. Do you have any recommendations for a formula that will create a list of numbers between two values, separated by a certain number? For example, all numbers between 8000 and 10000 seperated by 5. I'll need to do this many times and a formula would be extremely helpful!
Hello! To create a sequence of numbers, you can use the ROW function. The ROW function returns the row number. For example:
=80000+(ROW(A1)-1)*5
Copy this formula down the column.
Hi Alexander,
How to sequence the following please:
K0151/right
K0151/left
K0152/right
K0152/left
K0153/right
K0153/left
I am not good at it. Thank you for your help!
Hi! The formula below will do the trick for you:
="K" & TEXT(CEILING(SEQUENCE(45,1,1,1)/2,1)+150,"0000") & CHOOSE(MOD(SEQUENCE(45,1,1,1),2)+1,"/left","/right")
Hi, How do I sequence with sequence no depends on value on multiple cells/columns, for example:
Col-A Col-B Col-C Col-D (which is the sequence, which value starts can be random value), Thanks a lot!
111 RP C 05
111 RP C 06
AAA DW R 25
111 RP C 07
AAA DW S 55
AAA DW S 56
111 RP C 08
Hi! With Excel formulas, you can only make a sequence of numbers. You have text. Also, there is no pattern in your data that can be written as a formula.
I have a question, please help if you can.
I need a particular pattern in which every number -start from number 1- exist in every successive 5 cells in one column like from A1 to A5 filled with number 1
From A6 to A10 filled with number 2 and so on.
How can I do this?
Thanks in advance
Hi! If I understand your task correctly, try the following formula:
=CEILING(SEQUENCE(50,1,1,1)/5,1)
Hi,
How to convert range (column A1 to B2) into array in single cell for word office 365?
Example:
A1=4
B1=8
Convert array in single cell like this
Result: {4;5;6;7;8}
note:
Previous version word use below formula but not work on office 365.
{ =ROW(INDIRECT(A1&":"&B1)) }
Hi! Brackets are not needed in this formula: =ROW(INDIRECT(A1&":"&B1))
In previous versions of Excel, this formula was written as an array formula. In Excel365 you don't need to do this.
Hello,
I'm after a sequence for creating quote numbers, It would take the Year (2023) Month (04) - then a numerical sequence from 1 down till X.
From the date the quote was provided.
Unless the next month starts, then it would be 2023-05-1, etc
Say Cell Values of 2023-04-1, 2023-04-2, 2023-04-3, 2023-05-1, 2023-05-2, 2023-06-1, 2023-06-2, 2023-06-3, 2023-06-4, 2023-06-5.
3 Quotes in April, 2023
2 Quotes in May, 2023
5 Quotes in June 2023.
Effectively the counter resets at the start of each month.
Thanks
Hi! If there is some logical sequence in your data, describe it. Unfortunately, I do not see it.
Hi Alexander
Logical Sequence - Quote increments by 1 until such time as the month changes, then it resets back to 1.
However, the months do not have a set number of quotes, but will be dictated when a new quote opportunity comes in.
| A | |B|
2023-05 - 1 (May 2023, Quote # 1) Date is 10th May
2023-05 - 2 (May 2023, Quote # 2), Date is 15th May.
2023-06 - 1 (June 2023, Quote #1), Date is 3rd June.
So the number i would like sequenced is made up of 2 components.
Part A is a Function of the date that the quote is created. It uses the Year and Month,
Part B is an incremental step. Starting at 0, it adds an increment of 1 for each new quote created. This continues to increment whilst the Month the quotes are created are the same, when the new month rolls in, the number resets to 0.
So i see the sequence as an increment of 1 integer, then using a while or IF function, Whilst date of creation is X Month add 1 to integer and retain month/year (Part A). If month now is X+1 (May to June), then B=1 and month now set to X+1.
Hi! If I understand your task correctly, the following formula should work for you:
=TEXT(B2,"yyyy-mm")&"-"&(COUNTIF($A$1:A1,"*"&TEXT(B2,"yyyy-mm")&"*")+1)
Use the TEXT function to get the year and month from the date as text. The COUNTIF function will count the number of values with that year and month.
Sir maintaining a large number of business transaction data, we use "purchase" for every purchase transaction, "Sale" for every sale transaction and "Payment" for every payment etc. So i want to prepare a Purchase ledger. for this purpose i want to assign a specific reference number to 1st transaction of Purchase that number should be generated in sequence whenever any other purchase related entry posted.
Example:
5001 Purchase
9001 Sale
5002 purchase
4001 Sale
5003 Purchase
Thanks in advance
Hi! Enter the starting numbers in A1:A2 manually. The formula for A3:
=COUNTIF($B$1:B2,B3) + INDEX($A$1:A2,MATCH(B3,$B$1:B2,0))
After that you can copy this formula down along the column.
I want to create an excel sheet that records dates for maintenance of an equipment but for every time we update the date it should count so we know how many times we have done the maintenance. What can I do? If there are any equations to be written please assist.
I want to make a sequence that looks like this, divided by lines and collums, but it can only have 4 digits, from 1 to 1000
0001 | 0002 | 0003 | 0004 | 0005 | 0006 | 0007 | 0008 | 0009 | 0010 | 0011 | 0012 | 0013 | 0014 | 0015 | 0016 | 0017 | 0018 | 0019 | 0020 | 0021 | 0022 | 0023 | 0024 | 0025 | 0026 | 0027 | 0028 | 0029 | 0030 | 0031 | 0032 | 0033 | 0034 | 0035 | 0036 | 0037 | 0038 | 0039 | 0040 | 0041 | 0042 | 0043 | 0044 | 0045 | 0046 | 0047 | 0048 | 0049 | 0050 | 0051 | 0052 | 0053 | 0054 | 0055 | 0056 | 0057 | 0058 | 0059 | 0060 | 0061 | 0062 | 0063 | 0064 | 0065 | etc
Hi!
If I understand your task correctly, the following formula should work for you:
=TEXTSPLIT(CONCAT(TEXT(SEQUENCE(1,500,1,1),"0000")&" | ")," ")
For more information, please read: Leading zeros in Excel: how to add, remove and hide and TEXTSPLIT function in Excel: split cells / text strings by delimiter.
Hi, Could you help with the below:
We need to have a cell to enter the number, example 10. So, here 10 refers to the number of classes and we should get the same number of rows/columns to be added automatically to input the date & time for each of the class.
If I input 5 in the target cell. Then it should create 5 rows/columns for each date & time. The date & time should be in sequence.
Many thanks in advance
Hi! Your task is not completely clear to me. To recommend a formula to you, show an example of the result you want to get.
Is it possible to get number as below-
20220801
20220901
20221001
20221101
20221201
20230101
Hi! Here is the article that may be helpful to you: How to create a sequence of dates in Excel and auto fill date series. Use the TEXT function to convert the date into text in the desired format.
=TEXT(EOMONTH(DATE(2022,7,1),SEQUENCE(20,1,0,1))+1,"yyyymmdd")
I have a date usage sheet for our inventory where someone inputs the start date, how many days used, then the calculated end date. I have given the user 5 dates to set the start and days used. Is it possible to have the sequence function do all these with one function entry? Is it possible to have multiple starts and stops?
EX:
Start Date Days Used End Date
4/1/2023 21 4/22/2023
5/1/2023 4 5/5/2023
5/10/2023 8 5/18/2023
6/1/2023 10 6/11/2023
6/15/2023 3 6/18/2023
Sequence would go 4/1/2023 then fill the dates in until 4/22/2023, 5/1/2023......5/5/2023,5/10/2023.....5/18/2023 and so on.
Hi! There is no logical sequence in your dates. Add the number of days to the start date as described in this article: How to add and subtract dates in Excel.
I think this might work, if I understand what you're after:
=SEQUENCE(1, + 1,,1)
You could also calculate the by calculating the number of days between and to make it more dynamic.
Oops...used the wrong formatting in the formula.
=SEQUENCE(1,[Days Used] + 1, [Start Date],1)
Hopefully this shows up correctly
HI,
I have a set of data in one column, say Company Name in A1, PO No-A2,MRN Numbers in A3,A4,A5 etc. How do i bring these datas in different coulmn in sucha a way that All the company name in 1 coulmn, related POs in next column, MRNs in next column. I cannot use transpose since it will arrange all the data horizontally, not in a table manner.
Thank you
Hi! I don't really understand why you can't use transpose. To help you, give an example of the source data and the desired result.
Hi!
Tried figuring out through the well done article, but didn't manage to find my way.
I'm looking for the following to be implemented in a booking sheet I'm working on where certain conditions apply;
Once data has been entered in a specific cell, it generates a random number in another cell which will function as a reference to that booking.
The number generated must partly consist of specific values from two given cells, as in the following logic: "data cell 1" - "random number generated" - "data cell 2" .
The number generated is preferably in sequence which can not be duplicated, ie must respect what is generated in other cells (same column).
So in other words;
Once data has been entered in certain cells (column A) then the corresponding cell (column B) generates a random number with the above conditions.
Hope I was able to convey this understandably enough, and thanks in advance for any help!
Hi!
We have a special tutorial that can help to solve your problem. Please see How to generate random numbers in Excel without duplicates.
Perhaps this guide will be useful, too: How to get random sample in Excel without duplicates.
How do I sequence this?
Is there a formula?
Ex. Wk13 up to WK50
I want to sequence it and stop doing it manually
Create a sequence of numbers using the SEQUENCE function and combine it with a text string.
Try this formula:
="WK"&SEQUENCE(38,1,13,1)
Hi!
I have a set of project codes (example format is 90-00480-90GT001-ProjectName 1) in column D. The 90-00480 prefix is entered manually in column A, as is the project name suffix in column C.
In column B, the 90GT001 part is a sequential number, and I need to ensure that only the next available number in the sequence is used, when a new code needs to be generated. I've tried using the SEQUENCE function in column B and formatted the column to include leading zeros, then concatenated the prefix and suffix using the formula =A2&"-"&"90GT"&B2&"-"&C2, however this results in 90-00480-IT-90GT1-ProjectName 1 (the leading zeros do not pull through so the code format is incorrect).
How can I fix this?
Thanks
Hi!
To convert a number to a text string in the desired format with leading zeros, use the TEXT function:
=A2&"-"&"90GT"&TEXT(B2,"000")&"-"&C2
For more information, please read: How to add leading zeros in Excel
Absolutely fantastic! Thanks Alex!
1st row: 2000 0 0 2000 0 0 and so on over a month period. How to do it with a click?
Next step:
If i have multiple rows.. each has a different sequence of numbers.. how to fill a sheet over a month period (30 columns)?
Hi, how do I create a sequence based on values from another cell.
Example: Column "A" has values such as General Admin, Finance, Marketing, etc and I would like column B to generate a sequence such as below please.
Department Code
Finance FIN-01
General Admin GA-01
Marketing MKT-01
General Admin GA-02
General Admin GA-03
Marketing MKT-02
Marketing MKT-03
Marketing MKT-04
Finance FIN-02
Hi!
Use the COUNTIF function to set ordinal numbers for equal values. Use the TEXT function to write down these numbers with two digits.
=A1&"-"&TEXT(COUNTIF($A$1:A1,A1),"00")
This should solve your task.
Thank you so much!! it worked.
Hi, how to generate a sequence of say:
1,1,1 (3 times)
2,2,2 (3 times)
3,3,3 (3 times) e.t.c all in one column?
Meaning
1
1
1 (3 times)
2
2
2 (3 times)
3
3
3 (3 times) e.t.c
all in one column?
Hi!
To repeat the same number several times in a sequence, try this formula
=CEILING(SEQUENCE(50,1,1,1)/3,1)
Thank you so much Sir for your response.
It works absolutely, your answer really helped, thanks 👍
It's possible create a sequences like this 10,-10,20,-20,30,-30 ..... 100,-100 and start again 10,-10,20,-20 ???
Hi,
I would like to generate a reference number in sequential order every time a new data is added in the next row. Example:
In the A1: reference is “RED1”.
If a new data is added in A2 it will automatically generate “RED2” as a reference number. And so on. Which will look like something like this:
A1: RED1
A2: RED2
A3: RED3
This will be in relation to other data in the table.
Thanks
Hi!
Based on your description, it is hard to completely understand your task.
You can create a sequence of values with a formula like this.
="RED"&SEQUENCE(10,1,1)
But if you add some value to A2, then you can only replace it with RED2 using VBA.
by the way, I determined this by:
1. Copying the series of terms into Wolfram Alpha to identify an equation to produce the series --> a(n) = 5/2(-1)^n(-2n+(-1)^n-1)
2. Replicating the formula in Excel, using SEQUENCE(20) instead of x --> =2.5*((-1)^SEQUENCE(20))*((-2)*SEQUENCE(20)+(-1)^SEQUENCE(20)-1)
3. Modifying the formula to create a repeating series using MOD(ULO) function --> SEQUENCE(20) --> (MOD(SEQUENCE(A1)-1,20)+1)
Wow it works!!! thank you so much
Given A1 contains the number of terms you want:
=2.5*((-1)^(MOD(SEQUENCE(A1)-1,20)+1))*((-2)*(MOD(SEQUENCE(A1)-1,20)+1)+(-1)^(MOD(SEQUENCE(A1)-1,20)+1)-1)
Hi!
I don't think you can do it with a formula. Just write down those 20 numbers and copy down the column.
Hi, how to make sequence lets say of : 0 21 times, 20 15 times, 40 10 times, 60 6 times, 80 3 times, and 100 1 time; all of it in one row?
thanks in advance !!!
Hi! If there is a pattern in your sequence, describe it.
Hi, how to generate a sequence of say:
1
1
1
2
2
2
3
3
3
4
4
4
1000
1000
1000
e.t.c all in one column?
Hi! See the answer in this comment.
Hi
The below is a part of report, I need to add a numbers 1-7 in the end of the text for every new product description. Can you help please?
15 Pouch Box (595 * 241 * 125 mm)-1
15 Pouch Box (595 * 241 * 125 mm)-2
15 Pouch Box (595 * 241 * 125 mm)-3
15 Pouch Box (595 * 241 * 125 mm)-4
15 Pouch Box (595 * 241 * 125 mm)-5
15 Pouch Box (595 * 241 * 125 mm)-6
15 Pouch Box (595 * 241 * 125 mm)-7
4 Slice Clear Plastic Tray (156 Cut)-1
4 Slice Clear Plastic Tray (156 Cut)-2
4 Slice Clear Plastic Tray (156 Cut)-3
4 Slice Clear Plastic Tray (156 Cut)-4
4 Slice Clear Plastic Tray (156 Cut)-5
4 Slice Clear Plastic Tray (156 Cut)-6
4 Slice Clear Plastic Tray (156 Cut)-7
7 Month Caramel - RSPO SG (M/ SO2)-1
7 Month Caramel - RSPO SG (M/ SO2)-2
7 Month Caramel - RSPO SG (M/ SO2)-3
7 Month Caramel - RSPO SG (M/ SO2)-4
7 Month Caramel - RSPO SG (M/ SO2)-5
7 Month Caramel - RSPO SG (M/ SO2)-6
7 Month Caramel - RSPO SG (M/ SO2)-7
Hi!
To determine the sequential number of a product, use COUNTIF function.
Use this formula:
=A1&"-"&COUNTIF($A$1:A1,A1)
Thank you for quick response, you saved my life:)
Hi!
I want to generate a random sequence based on previous data.
E.g., Column A - E has values from 1 to 45
So if I have 100 rows of such a sequence, is there a formula that can predict/generate the next sequence?
Hi!
To generate random numbers, use the RANDBETWEEN function.
How to predict the series for
100
200
300
500
600
700
900
Hi!
Please read the above article carefully.
Hello,
I need a formula that would return the sequence for this below:
Part Number: Sequence: (Formula for this)
47009 1
47009 2
47009 3
47014 1
47014 2
Hi!
What is the pattern in your data?
Hello,
There is no pattern as each part number repeats itself differently. I need the sequence to add different descriptions to each one.
Hi!
A sequence can be created if there is a pattern.
How can I generate a 5-digit PID with a check digit on it let's say a batch of 100 PIDs?
Hi!
We have a tool that can solve your task in a couple of clicks – Random Generator. It offers a variety of ways to get random values, which you can look out at this link.
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
To use the formula, read the article above carefully.
Hi
How do I present such data in a sequence.
1- 51
51-101
101-151
Hi
Use the concatenation operator (&) to concatenate numbers into a text string.
=SEQUENCE(10,1,1,50)&"-"&SEQUENCE(10,1,51,50)
if i have the minimum and maximum of different batches of serials in two columns, which function can i call to know if a number is between the two columns. For example, min: 1 and Max: 100 in two columns, how do i find out if 15 is contained in that range for different range and different number instead of having to open up the 1 to 100
Hi!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you: IF AND in Excel: nested formula, multiple statements.
Hi, how do I create a sequence by 1 in a cell that depends on the max number of other cells (H) and if it is selected "NEW" (C) and also depends on the year (F)
I have this formula: =MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1
However, a year can have two max numbers which I need to take the sequence from; for instance, 110943000 and 110943700. I would still need the sequences on 110943001, 110943701, 110943002, 110943702, and so on. The formula is only giving me the sequences from 110943700 which is the maximum.
C18 = NEW
D18 = 110943000 (ID)
F18 = IFERROR(VLOOKUP(D18,$D$3:$F$17,3,FALSE),"-") = 9 {this is how the year is calculated), there's no issue with this one}
H18 = MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1 = 110943701 {instead of 110943001}
it only works in this case:
C18 = NEW
D18 = 110943700 (ID)
F18 = 9
H18 = MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1 = 110943701
Thank you!
Hi!
If I understand your task correctly, use Excel LARGE function to get n-th highest value. Extract the two maximum numbers and add 1 to each of them.
Hello,
I need advice on arranging around 100,000 phone numbers in sequences. For example, i have below list of numbers:
1001023
1001024
1001093
1001094
1001095
4677662
4677663
4677664
4677665
4677666
4677667
4677668
4677669
In ascending order, first 2 numbers are in a sequence of 2, next 3 numbers are in a sequence of 3, remaining numbers are in a sequence of 8. So i want to get a value of 2 against first 2 numbers, value of 3 against next 3 numbers and value of 8 against all the remaining numbers. Kindly advise.
I have a situation in Excel where there is one workbook and it has 6 worksheets in all.First one is a MasterSheet and other
5 sheets are named as S1,S2,S3,S4 and S5.Now I want to link these 5 five sheets with the Master at various cells in a column
sequentially.
Like in S1 E1 shud link with F2 cell in Master,in S2 E1 with F3,in S3 E1 with F4,in S4 E1 with F5 and in S5 E1 in F6 cell in Master.
How can it be done ?
Looking forward to hearing from you.
Hello!
If I understand your task correctly, the following tutorial should help: How to create external reference in Excel to refer to another sheet or workbook.
I would to have my out would like this. Kindly help us
Product Brand Sequence
A1 Apple 01
A1 Apple 01
A1 Mango 02
A1 Mango 02
A2 Mango 01
A2 Mango 01
A2 Mango 01
A2 Mango 01
A3 Banana 01
A4 Orange 01
A1 Mango 02
A2 Mango 01
A3 Banana 01
A4 Orange 01
Hi!
I am not sure I fully understand what you mean.
Hi,
I need to get the following formula to work and I can get it to come up except it's removing the 00 and treating the second last number as a negative and then minus the last number as well I realise this is due to the hyphen/subtraction symbol just wondering if there is a way to bypass this?
ABC-001-10412-7
ABC-002-10412-7
ABC-003-10412-7
ABC-004-10412-7
The formula I have used is - ="ABC-"&(SEQUENCE(004,,001)&-10412-7) - excel is removing the 00 after I enter of course and the result is
ABC-1-10419
ABC-2-10419
ABC-3-10419
ABC-4-10419
Assistance would be greatly appreciated.
Thanks
Hello!
When concatenating text values, use quotes. To display leading zeros in a number, apply these guidelines: Leading zeros in Excel: how to add, remove and hide.
If I got you right, the formula below will help you with your task:
="ABC-"&TEXT(SEQUENCE(4,,1),"000")&"-10412-7"
I hope my advice will help you solve your task.
Thank you so much yes it did!!
Hello, I need help with a formula for this... I'm out of ideas..
A B
2 = -2000
(2,5= -1750)
3 = -1500
4 = -1000
5 = -500
6 = 0
7 = 500
8 = 1000
9 = 1500
10 = 2000
Thank you very much for any help.
Hello!
If I understand your task correctly, try the following formula:
=-2000+(A1-2)*500
HI I need to have the data like the following -
Part1
Part1
Part1
Part1
Part2
Part2
Part2
Part2
and so on till 2010 rows and "Part1" should apply to 40 rows and it should increase the counts like Part2 part3 .... till it reaches "Part50" to fill up 2010 rows .
I believe my query is somewhat similar to your latest reply to a user's query -
=CEILING(SEQUENCE(300,1,1,1)/3,1)+44858 only it had dates repeated like -
Oct 25
Oct 25
Oct 25
Oct 26
Oct 26
Oct 26
Please let me know the quickest and most effective approach to accomplish this. Your assistance would be highly appreciated.
Hi!
Combine string and formula to get a sequence of text.
="Part"&CEILING(SEQUENCE(2010,1,1,1)/40,1)
Hi
I would like to create a series for dates from say Oct 25 - Dec 10 with 3 repetitions of each day
So I would like my series to look like this:
Oct 25
Oct 25
Oct 25
Oct 26
Oct 26
Oct 26
Oct 27
Oct 27
Oct 27
And so on till December
Could you please tell me a quick and efficient way to do this? I would greatly appreciate your help.
Hello!
Since dates in Excel are numbers, create a sequence of numbers as described in the article above.
=CEILING(SEQUENCE(300,1,1,1)/3,1)+44858
Set the date format in the cell.
Hi,
I have a data which I need to sequence/group. I actually don't know if its possible (still crossing fingers that it is possible).
For example, document number 1, 2, 3, 9, 10, 25, 26, 28, 30, 31.
I need to summarize it to 1-3, 9-10, 25-26, 28, 30-31.
Is there a way to do it?
Hi!
There is no logical pattern in your grouping.
Hello,
I need to create the following sequence
149597883002568 (360360)
149597882642208 (360360)
149597882281848 (360360)
149597881921488 (360360)
149597881561128 (360360)
149597881200768 (360360)
149597880840408 (360360)
149597880480048 (360360)
149597880122691 (357357)
149597879762331 (360360)
and so on
1 to 8 is 360360, 8 to 9 is 357357 and 9 to 10 back again to 360360
Any advice?
Hello!
To determine every eighth value, use the MOD function with a divisor of 8.
=IF(MOD(SEQUENCE(100,1,1,1),8)=0,357357,360360)
This should solve your task.
First thanks for answering but I'm sorry, I think I'm not really detail when I explaining
I need to create sequence that the result is looks like this
1. 149597883002568
2. 149597882642208
3. 149597882281848
4. 149597881921488
5. 149597881561128
6. 149597881200768
7. 149597880840408
8. 149597880480048
9. 149597880122691
10. 149597879762331
The first to eight values always increases by 360360, but from eight to nine value, it increases by 357357, nine to ten value is back again to 360360
And I need to create that sequence more than 100
Hi!
Sequentially add these numbers to the original number. If the sequence is written in B1, then the formula in C1 can be:
=$A$1+SUM($B$1:B1)
Copy the formula down the column.
How to make a sequence more than 100 - read the instructions for the SEQUENCE function.
Also note that in your example you're subtracting numbers. In addition, Excel displays such large numbers (more than 15 digits) in exponential format. To see it in full, convert the number to text using the TEXT function.
Hello,
I'm trying to figure out how to sequence
a3i5a1p
a3i5a2p...
a3i5a9p
a3i5aap
a3i5abp...
then group them in a range of 25. a3i5 and the p never change, and the other two switch between a-z and 0-9. I am working with excel 2013.
If i have raws as following
1
2
3
5
6
7
10
12
I need to write " 1To 3,5 To7, 10,12"
Hello!
To combine numbers and text, use this guide: CONCATENATE in Excel: combine text strings, cells and columns.
Hello!
Place 42 characters 0-9 a-z in a separate column. For example, in Z1:Z42.
To dynamically refer to these symbols, use the INDIRECT function.
="a3i5"&INDIRECT("Z"&(CEILING(ROW()/42,1)+10)) & INDIRECT("Z"&(TRUNC(MOD((ROW(A1)-1)/1,42)+1)))&"p"
For more information, please visit: Creating indirect references from cell values and text.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.