If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".
I can generate the consecutive numbers, but have not figured out how to generate the required string.
If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".
I can generate the consecutive numbers, but have not figured out how to generate the required string.
Let me see if I can explain my question in an understandable fashion....
I have a table containing data for about 2000 ID numbers. Some of these numbers are unique and some are duplicates. I would like to convert the ID numbers into a consecutive list of integers while preserving the unique numbers. For example, if the first column of my table is currently:
ID#
18578
19644
19644
20247
20974
21361
21361
21419
I would like to change that to something like:
ID#
1
2
2
3
4
5
5
6
I need to know which records (i.e. which rows of data) are from the same ID# but want to remove the actual ID#.
I have an interval, for example [1431, 1589] an I need to automatically generate consecutive numbers in this interval - for example 1431.1 , 1431.2 ..... 1588.8, 1588.9, 1589. How can I do that? I know I can do this manually by writing some values and drag down but I need this done automatically.
I've been using excel for quite sometime but have never tried the macro system or posting VBA code so I'm wanting to get my feet wet with a little project.
I want to make a button on a sheet that when pressed generates a random number between 3 and 18, ideally it would make a new set of random numbers if pressed again.
Is there any script that I generate 6 numbers of these numbers from the list? so they do not recur after another example 7,12,13,19,26,33
View 2 Replies View RelatedI am trying to create a column which lists numbers consecutively from 1 - 10,000, grouped in intervals of 20 (ex: 1 - 20, 21 - 40, 41 - 60, 61 - 80, ...) without having to enter them all manually. Is there anyway to do this in Excel?
View 3 Replies View Relatedmodifying the following VBA code; this code auto-generates 4-digit unique numbers, using zero as one of the starting digit as well. I need the 4-digit numbers NOT to begin with a zero, the 4-digit numbers should only begin with numbers 1-9.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Test As Integer
[Code]....
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
I'm trying to set up a stocktake sheet for one of the business' I work for. We use a letter = number cost pricing code on our products. ie: H=1, A=2, R=3, D=4, T=5, O=6, G=7, U=8, E=9, S=0. I would like to have 4 columns where A contains the qty, B the alphabetical cost code, C the deciphered numeric per each amount and C being the total price (ie column A x C).
View 11 Replies View RelatedI would like to share with you this VB Code
convert number to text ( Convert Numbers to Text.) I hope you like it
Add it to add-in to be able using it in all excel sheets.
Im trying to make some code to go through a list of numbers, and pick the next highest number from the one entered in a form. Then I need to do some processing with that number (i need to create a worksheet with that number as the name, and place that number in a few cells on that worksheet, and the main worksheet, but thats all stuff i think i can do).
View 14 Replies View RelatedI'm looking for a formula which returns the longest consecutive series of occurrences of 2 chars in a text string.
In the case I'm trying to count the longest consecutive number of nine-spares (9/) in a bowling game.
For example:
|7/|X|9/|9/|9-|9/|9/|9/|9/|X8/|longest series of 9/ would be 4.
|9/|9-|9/|9/|7/|9-|(8)/|X|7/|9-|longest series of 9/ would be 2.
|9/|(8)1|X|X|72|63|7/|8/|8/|9/9|longest series of 9/ would be 1.
I've attached an example of the data I am trying to solve this problem for. Basically I am looking for strings of 26 blanks or more in a row, but I want to determine the ending location of the last string of 26+ blanks. I've been able to determine the location of the maximum string of blanks, but that max group of blanks isn't always the last set over 26.
Any link to find the location of last string of blanks of 26 or more? The example I attached is a good example since there are two strings of 26+ blanks with the last one being shorter. I've also include the answer I'm looking at the end of the data in the example.
Example.xlsx
I'm trying to use logic to identify trends...in each of the strings below, I want to count the occurrences of the left most character appearing consecutively. The answer is to the left
I'm trying to do this via a formula vs. vba if possible
BBBBBBBBBBBBBBBBBBB - 19
BBBDUDUUUUDDBBBBBBB - 3
UUDUDDUUUDUDBBBBBBB - 2
UDUDDUDUUUDDBBBBBBB - 1
[Code] .....
I have 3 columns, in column 1 and 2 there will be numbers and I want automatically to get in column 3 the range of numbers between Column 1 and Column 2
Column 1 - 100
Column 2 - 500
Column 3 - 100, 101, 102, ..., 500
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
View 5 Replies View RelatedIn column B & C, starting at row 2, there are first names & surnames. In column H there is a grade for each name, eg B. Columns AC through AH hold subject codes for each name, eg 18E/Hs1 (potentially all columns could contain a code or only 1). The identifying part of the subject code is the first 2 letters after the / . So in the example the subject code 18E/Hs1 the 'Hs' signifies it is History. A table of subject code and their subject name is contained in a named range called Subject_ID (see below). I intend to make this range dynamic.
Subject .Subject
Code......Name
HsHistory
EnEnglish
ChChemistry
ITIT
PhPhilosophy
MpMaths (Pure)
PtPart-time
PyPhysics
SoSociology
BsBusiness Studies
SSSports Science
FrFrench
BiBuilding.
For each name (starting at row 2) I want to achieve the following: Scan across the range containing the subject codes (AC:AH), identify the first two letters after the / and match it to the subject name in the list. Paste the subject name to a cell starting at AI1 and then insert the grade (contained in column H) for that student in the corresponding subject column. The next unique subject name should then miss a column be pasted in AK1.
Example
......B ......C........H.. AC..........AD...........AE..........AF........AG.........AH.........
Matt........Pia......D....18A/Hs1...18B/En1....18B/Hs3
should result in the word History in AI1 and English in AK1 and the letter D in AI2 & AK2. Note as there is already a reference to History this is not repeated again.
I hope this is clear. I have enclosed a sample workbook with expected output and colour coded the subject names so that the order that they are pasted in is evident.
I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like:
1
1
1
0
1
0
0
0
1
0
0
1
I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.
i have a list of numbers in a column, and i have sorted it smallest to largest, now i wish to identify which ones are consecutive in the column.
5184730512
5184730547
5184730763
5184730766
5184730767
5184730836
5184731135
5184731136
5184731149
5184731212
5184731222
5184731389
with this Excel problem? I have a set of data of 300 some odd rows of numbers. I need to find 24 CONSECUTIVE values that add up to the HIGHEST sum? For instance,
2
2
0
0
4
2
0
0
1
8
5
2
I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like;
1
1
1
0
1
0
0
0
1
0
0
1
I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.
I am preparing a attendance sheet. I am using 1 & 0 for present(=1) & absent(=0). I want to find out if a student has been absent for three consecutive days and if there is three consecutive 0 then the formula should return the value 0 ( the student gets 0 if he is absent for 3 consecutive days ) otherwise it should add all the 1s in the row. i.e
1 1 1 0 0 1 1 0 = 5
10 0 0 1 1 0 0 = 0
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
I want cell D28 to be text of the location to the file selected through CommandButton1.. tia
This is what I have so far:
Code:
Private Sub CommandButton1_Click()
Dim browse As Long
Set Application.GetOpenFilename("All PDF files (*.pdf*), *.pdf*", , _
"Choose a Filename") = browse
ActiveSheet.Range("D28") = browse
End Sub
I need a formula that will count the number of consecutive 3 0's from the following Data series. There are 22 such events.
0
6
15
[Code].....
I have the attached table of numbers and I need a formula at the end of each column to identify whether any cells in that column consecutively have numbers in them greater than zero. Ideally by a count of how many cells in the column have consecutive numbers greater than zero (so if there are three 1's in a row and then a zero and then another 2 1's I want it to count 5).Excel Help.xlsx
View 2 Replies View RelatedI have a spreadsheet that has a column of numbers some of which are consecutive, some of which are not. I would like to have a way to lump all of these chunks of consecutive blocks into ranges. For example:
2759
2760
2761
2762
2764
2765
2766
2768
2769
2773
would return something like:
2759 - 2762
2764 - 2766
2768 - 2769
2773
Any ideas?