Create String Function With Range Of Numbers
Sep 11, 2012
Is it possible to create a string with a range of numbers
1
3
4
5
6
8
10
Result: 1,3:6,8,10
Or
1
2
3
4
Result: 1:4
Or
1
3
5
7
Result: 1,3,5,7
Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.
Function AConcat(a As Variant, Optional Sep As String = "") As String
'By Harlan Grove, March 2002
Dim Y As Variant
If TypeOf a Is Range Then
For Each Y In a.Cells
[Code] .........
View 9 Replies
ADVERTISEMENT
Aug 20, 2014
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
View 4 Replies
View Related
Aug 6, 2012
I have a column A with following values below:
Column A
"VL50s"
"M50s"
"H50s"
"VL50s"
"H50s"
I would like to extract the numbers and run the following arithmetic function below into column B.
key:
x is a number
VLx --> (x) + 1
Mx -->(x) + 2
Hx --> (x) + 3
the output should look like the following using the key above:
column B
51
52
53
51
53
View 9 Replies
View Related
Mar 25, 2014
I'm working on a long list of cells that have multiple entries of the same part number at different point on the list. I want to create a function that consolidates those part numbers in two columns to the right of the raw count.
PART
QTY
CONSOLIDATED PARTS
CONSOLIDATED
QUANTITIES
View 3 Replies
View Related
Dec 1, 2007
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005
*
View 10 Replies
View Related
Apr 2, 2014
I have a range of cells containing a string of numbers separated by ';' e.g.
COLUMN A COLUMN B
IDENTIFIER_011122;1942;2011;1869;2642;2729;2731;2655;2678;2341;2347;2354;2332;2506;1809
IDENTIFIER_021122;1942;2011;1869;2642;2729;2731;2655;2678;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_031122;1942;2011;1869;2642;2729;2731;2732;2648;2255;2207;2217;2220;2387;1880;2506;1809
IDENTIFIER_041122;1942;2011;1869;2642;2729;2731;2732;2678;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_051122;1942;1971;2011;1869;2642;2729;2731;2648;2678;2342;2354;2355;2207;2209;2217;2220;2398;1880;2506;1809
IDENTIFIER_061122;1942;2011;2169;1869;2642;2729;2731;2732;2678;2341;2343;2347;2354;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_071122;1942;2095;1869;2573;2587;2648;2655;2673;2207;2209;2220;1880;1809
IDENTIFIER_081122;1942;2095;1870;2642;2648;2673;2678;2341;1880;2506;1809
I am looking to find the top 5 most common/repeated numbers within this range. I have found a formula to work out the largest number within the range:
=MAX(IFERROR(0+TRIM(MID(SUBSTITUTE(";"&B3,";",REPT(" ",LEN(B3))),(ROW(A$1:A$200)*LEN(B3))-1,LEN(B3))),0)) (REF http://www.excelforum.com/excel-gene...e-numbers.html)
How to modify it to work with e.g. the mode function.
I know I could separate all the numbers out using 'text to columns' but I will need to work this out regularly on large quantities of data ...
View 13 Replies
View Related
Jul 8, 2014
I'm working with some diagnostic messages produced by a communications network, and trying to isolate a certain kind of message. There are many different types of messages contained in the error logs, and I need to look at different ones at different times. As a single log may contain thousands of messages, I'm trying to use VBA to grab the ones I want. The easy part is that these logs can be exported as CSV files.
Each message contains an identifier which I can use for finding the correct messages of a given type. (I'm only looking for one type at a time.) However, this identifier is not on the first line of the message; it's buried a few lines down, and the number of lines is variable. Additionally, the length of the message is variable. The good news is, all messages of interest start with the same text string, and all of them end with another consistent text string. It looks something like this:
Message Start
blah
blah
Message Type
blah
blah
Message End
(Where "blah" is useful information that varies, and the number of lines of blah varies.)
The Message Start string is constant across all message types, as is the Message End string, so I can't use them to find the correct Message Type. However, they are still useful for marking the start and end of each message.
I'm using the find function to find the correct message type, then another find (going up) to get the start of the message, and putting the row number into a variable (msgStartRow). Then I find again for the end of message (msgEndRow).
That all works just fine.
What I can't figure out is how to turn those message row numbers into a range that can then be copied and pasted onto a separate worksheet.
How can I use these variables to copy the correct range? For example, this is what I recorded manually, but it's not intelligent. It can't accommodate different message lengths:
Code:
ActiveCell.Offset(6, 0).Range("A1:A145").Select
Selection.Copy
View 6 Replies
View Related
Jan 22, 2014
I am looking for a function that gives a range of numbers a score, here is what i need:
if A1 is between 6&15 B1=25 if A1 is between 16&35 B1=15 if A1 is between 36&65 B1=5 if A1 is 66 or more B1=0
View 5 Replies
View Related
Jul 18, 2006
I have a range of numbers between 1 and 25 in 50 cells.
How could I use the countif function to look at that range of numbers for
values between 5 and 10 and count those values that meet the criteria of
between 5 and 10?
Or if there is another function to use, let me know.
View 11 Replies
View Related
Oct 26, 2012
is it possible to use the VLook up function to look at a range of numbers - I.e.
30-40, answer = 15%, I have tried 30-40 also 30,40 but none seem to work?cualte?
View 4 Replies
View Related
Sep 15, 2009
I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.
ex. average A1:F1 if G1:L1 equals M1 or M2
View 3 Replies
View Related
May 7, 2013
I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:
Original unsorted data:
79
45
23
56
1
The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5 So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range) Here is what I have.
I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:
Function CSORT(r, p)
Application.Volatile
'rs or something similar that will contain the sorted range r
Dim rs As Range
'Sort rs (The following line one doesn't work, so I need one that does)
rs = Application.Range(r).Sort
Dim p As Integer
[code]....
View 1 Replies
View Related
May 13, 2006
i am trying to convert a range of numbers to text using the Cstr function ,it doesnt work. excel VBA help states that CStr function converts a numeric value to a String. i am aware that i can convert a number by preceding it with an apostrophe
i just need to know why Cstr doesnt do the job
Sub covrt()
For Each rng In Selection.Cells
rng = CStr(rng.Value)
Next
End Sub
View 3 Replies
View Related
Jun 11, 2008
I have a field that contains the following: 012100002030
I need a formula that will tell me whether or not the string contains a number higher than 2 or whether or not it contains more than one 2.
Examples:
001000002011
111111000022
401110000000
the first loan would not meet the criteria as it contains no number higher than 2 and only contains one 2.
the second loan would meet the criteria as it contains two 2's.
the 3rd loan would meet the criteria as it contains a number higher than 2.
View 9 Replies
View Related
Jun 6, 2008
I have two questions. Below is a code snippet that demonstrates what I'm trying to achieve.
Const D_WIL_SearchString As String = "[HTML ON]*<a title=""*"" target=""blank""href=""*"">*</a>*[HTML OFF]"
If D_Info(i).WIL Like D_WIL_SearchString ThenDo AElseDo BEnd If
The double quotes ("") are supposed to represent a single double quote within C_WIL_SearchString.
The asterisks are supposed to represent any characters of any length.
All other characters (except for the initial double quotes (")) should be present within the string D_Info(i).WIL
I'm having issues ensuring that this code works correctly though, as it does not appear to search for the string that I'd like it to.
As a second question, I would like to know if there is a way of ensuring the asterisks (or another similar character) only allow whitespace? (i.e. spaces and new lines)
View 6 Replies
View Related
Oct 13, 2009
On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
View 5 Replies
View Related
Oct 24, 2011
I have the following code.
Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array("ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"), Operator:=xlFilterValues
The values ELDU238 - ELDU247 are values in a column beginning at A2. I want to create
"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"
in a string variable strArray which will enumerate whatever values are in column A beginning at A2 and create the above. I can then have
Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array(strArray), Operator:=xlFilterValues
Hence if the number of entries changes e.g. say I have
"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247", "ELDU248"
the code will still work.
View 4 Replies
View Related
Dec 5, 2008
i have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. I currently have this code but it keeps returning an error message. anyone know how i could do this???
Sub nSheet()
Dim nm, i As Long
nm = Worksheets(Worksheets.Count).name
i = 1
Sheets.Add
Do While WorksheetExists(Left(nm, Len(nm) - 1) & i)
i = i + 1
Loop
ActiveSheet.name = Left(nm, Len(nm) - 1) & i
End Sub
View 4 Replies
View Related
Nov 9, 2011
I need the code to create a find box into which a search string is input. The code should find the string at a particular cell, then copies this cell and four cells to the right to a location in sheet 2 of the same workbook
The code runs but comes back with a runtime 424 error. I believe the issue is being caused by "nextCell" but I cant see why.
Sub FindStrings()
Dim firstCell, nextCell, stringToFind As String
' Show an input box and return the entry to a variable.
stringToFind = _
Application.InputBox("Enter J Number and Stage Number. For Example J1234 ST1", "Search String")
' Set an object variable to evaluate the Find command.
[Code] ........
View 3 Replies
View Related
Jul 23, 2012
I have this formula, ( which i found the basis of on a You tube video and Richard Scholar was accredited with improving the soloution)
=SUMPRODUCT(--ISNUMBER(F4:AH4)*10^{-29,-28,-27,-26,-25,-24,-23,-22,-21,-20,-19,-18,-17,-16,-15,-14,-13,-12,-11,-10,-9,-8,-7,-6,-5,-4,-3,-2,-1})
This forumla generates a number for each player, the higher the number the more inline they are to get a game
Problems are this works for the 29 weeks of this year but more weeks need added to the end of the year as we get there. Can i generate the array numbers from a formula and shorten.
This is a sample data ignore row 2( just a count of players) and data actually goes back to 6th Jan
Excel 2007BCDEFGHIJ1Wk21Wk22Wk23Wk24Wk25Wk26Wk27Wk28210101010101010103Player 1111104Player 210001105Player 300101016Player 4010101107Player 50110118Player 600009Player 7111000110Player 8011100011Player 9000012Player 101011113Player 11101114Player 121010115Player 13016Player 141010017Player 15118Player 160101Sheet1
View 7 Replies
View Related
Jan 5, 2009
i have a question regarding the right function. can you use a string within the function. i know that the function is written =right(A3,2). is it possible to write the function as =right(a3:z3,2). i keep geting an error with this function
View 4 Replies
View Related
Jan 26, 2007
I have a Sum function in a cell on a spreadsheet and a bit of code which checks it using an If statement. If the Sum equals 1 (100%) it should do one thing, if not do another. However, if I put in the If statement that the Sum cell value should equal 1, it doesn't work. If I put it should equal "1", it does work. Why?
Example that works:
If Range("LocalBirthTypes").Value <> "1" Then
MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool"
Goto Exiting
End If
Example that doesn't work:
If Range("LocalBirthTypes").Value <> 1 Then
MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool"
Goto Exiting
End If
View 8 Replies
View Related
Mar 31, 2014
I maintain a spreadsheet to track monthly sales of a few thousand items (see attached sample data). I'd like to have a formula that would sum only the last 12 months in the range of data. It would need to ignore all of the data before and the blank cells after the 12 months.
It's difficult to update the range each month for all of the products.
View 5 Replies
View Related
Dec 3, 2009
I have data like follwing strings.
WAY-308312-1
WAY-521340-1
233-6767-676-6
First two strings are correct. If I get number like in third string first part of string then we have to move row to some other sheet.
The first part of string should have text like WAY-123-13-1.
I used Val function it checking first charector only. It the string is like 2A1A-243-233-2 the it is not checking.
View 14 Replies
View Related
Aug 25, 2009
I would like to extract the digits below in red. The variables are the digits in all of the sections vary from a minimum of one digit to the maximum of three digits.
111.222.333.444
View 2 Replies
View Related
Feb 1, 2012
I need a formula to extract the numbers from data in a cell:
Examples of data in cells:
G 622 ENTERTAINMENT ( I need to extract 622 )
D 6129 TOYS ( I need to extract 6129)
C 1039 Toddler TOYS ( I need to extract 1039 )
R 05 VEHICLES (I need to extract 5 )
All the cells have spaces before an alphabetical identifier, then a space ( it could be more than one in some cases ), then a number ( between 1 and 4 digits ), then a space and a description.
View 9 Replies
View Related
Nov 15, 2013
The table below is a portion of a calendar to track vacation and sick time. What I am trying to do is add all the hours of vacation ("V") and sick ("E") time that an employee has used in a cell at the end of each row which represents a month. So there are 31 cells that I need to look in and if it contains V... sum in the vacation cell. There may be a space after the letters and there may not be. They can also start with a decimal.
month
1
2
3
4
5
Vacation
Sick
January
E8
V8
V.5
V6.25
E .5
Sum of V (should be 14.25)
Sum of E
View 6 Replies
View Related
May 21, 2014
I am struggling with a problem which involves a column with both the text and nos in that. I want to calculate the following formula.
Total = the Sumproduct of the nos in the description*Length*Width*Height.
In the description column,
1) The nos will be varying i.e x*x or x*x*x or X*x*x*x*.
2) After the nos there will not be any data.
Example table:
Work Description
Length
Width
Height
Total
Parapet wall 2x4x8
12
2
4
6144
[Code] .......
Is there a way to achieve this, what is the formula for this ??
View 9 Replies
View Related
Jan 9, 2007
I am looking for formulae to identify the first 20 non-zero numbers from a string of alphanumerical and non alphanumeric characters
EG If column A any data such as:
123
0123
01(2)3
0.123
123abc
0123abc
01(2)3abc
0.123abc
a1b2c3
1a2b3c
Then the corresponding row for column B would return 123
Using help from my maths Prof and some IT bods, i have so-far come up with the array-formula:
=1*MID(A7,MATCH(FALSE,ISERROR(1/MID(A7,ROW(1:100),1)),0),20)
assuming that the total string is not more than 100 characters.
View 7 Replies
View Related
Apr 7, 2007
is there a way to have the number formatting in Text() variable? For instance, I want to reference a cell that can change between $1.00 and $10,000,000.00.
Let's say that in A3, the number turns out to be $10,000.00.
When I put in text(a3,"$0.00") it spits out $10000.00. But if I go the other route and go text(a3,"$000,000,000) it displays "$000,010,000.00".
View 9 Replies
View Related