Get Same Length Series Of Numbers From Different Columns In The Same Cell?
Oct 2, 2012
solve this task with a formula or a macro?
I have this strange number data stored by columns. Each cell contains one number series of different length from 1 to 13.
Needed results 1, Needed results 2
7409998765 7409998766 , 9998766567876, 7409998765 9998766 9998766 7409998766 9998766 9998766567876 9998766 9998766
I need to look-up and bring in one cell for each row only the 10 length numbers separated by space and in another cell the 13 length numbers space separated also.
The number of columns containing data is different from row to row ( from 2 to 60)....and the rows..are thousands..
View 7 Replies
ADVERTISEMENT
Feb 20, 2007
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View 2 Replies
View Related
Jan 9, 2010
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
View 9 Replies
View Related
Oct 12, 2010
I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.
example:
1
2
3
4
8
9
becomes
1
2
3
4
8
9
View 7 Replies
View Related
Mar 14, 2008
I want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example.
Series
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
and i want to delete 1 to 5 numbers delete to shift cells up.
View 9 Replies
View Related
Apr 18, 2007
When I tried len(nYear) where nYear is a number like 2006, I don't get the number of characters in the number. I know that's confusing strings with numbers, but...
Is there a VBA function that returns the number of numbers deep in an integer; ex. if nYear = 2006, the function would return 4?
I could easily do a < or > line of code, but I'm just curious if there's another way to do it.
View 9 Replies
View Related
Mar 5, 2013
I have a spreadsheet with numbers like this
9404388
9404374
9404391
9405695
38301663
9409724
1791016
9435145
9530758
9440464
9441182
71000569
9467438
There are a thousand numbers like this, I was wondering how I can move the cells with 7 numbers to the right column, and keep the cells with 8 numbers where they are (or move them to the 3rd column to the right)
View 14 Replies
View Related
Mar 27, 2009
I have a column of contract numbers of varying length. I want to run a sumif of all of the values on my reference sheet that have the contract number beginning with the contract number in my listing. All of the the contract values in my reference sheet are very long. Since the numbers I am working with vary in length, i don't know how to match this string in the sumif function
I want something like this
=SUMIF('Reference Sheet!$A$5:$A$13410,LEFT("", LEN('Select Contracts'!$C6))='Select Contracts'!$C6,'FINALIZED DATA'!$H$5:$H$13410)
Is there a way to refer to the range in the sumif function,
View 9 Replies
View Related
May 24, 2014
a formula to extract the numbers into two separate fields. The text may vary in length and the numbers vary in length also from 1 digit to 2,000,000.
Data in A1 is as follows:-
Meter reading Old:1345 New:67890
View 9 Replies
View Related
Jul 26, 2013
how can I make this simple macro sum columns that will be of variable length? I would like the sum to appear as the last entry in that column and highlighted.
VB:
Range("H36").Select
Selection.FormulaR1C1 = "=SUM(R[-34]C:R[-1]C)"
Range("J36").Select [code]....
View 7 Replies
View Related
Jan 22, 2006
I have a SS I would like to reference a cell in a column which changes in
length. In this case, I would like to reference the most current date in a
column.
Is there a simple method for doing this, as the data will routinely be
different lengths in that column.
Further, there is Data on the Horizontal that I would like to reference to
another cell in another sheet that would correspond to this date. Can anyone
give any suggestions as to the best way to accomdate this?
View 11 Replies
View Related
Jul 20, 2012
I have 5 columns with data in each
I want to create a 6th column that looks to the columns on the left with data in ti and concatenates all data in the 5 columns and puts it into one cell in the 6th column however put a space between each break of data so that it can be distinguished which bit of data was in what column previously.
The challenge is the new 6th column can only contain 30 characters - When it exceeds 30 characters then create a 7th column and put the rest of data in the 7th column, again the 7th column can only have 30 characters so if exceeds this then put the remaining characters in a 8th column
There will never be more than a total of 90 characters in the original 5 columns so there will only need to be scope for a maximum of 3 additional columns
So for example
Column A had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column B had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column C had a word that contained 10 characters
Column D had a word that contained 5 characters
Column E had a word that contained 10 characters
Then the result would be
Column F would only have the data originally held in Column A (because it can't include Column B's data as this would exceed the 30 characters)
Column G would have data that was originally held in column B and column C - with a space between B and C data
Column H would have data that was originally held C, D and E - with a space between C, D and E data
Another point to consider is if in one of the orginal 5 columns had say 3 words in it and lets say the 3rd word is the word that exceeds the 30 character limit, then the whole of the third word is to be carried oved to the next new column, I can't have words cut in hlaf with one half in Column 'F' and the other half in Column 'H' for example.
View 1 Replies
View Related
May 2, 2012
I have a column of data where the seperate entities are divided by blank rows. for example: i need to calculate sum(G3:G15) then skip the blank row (G16) and calculate the next group, say sum(G17:G50) and export that import to a new worksheet.
The issue is that the column lengths vary from day to day so the blank rows will not be in the same position.
View 2 Replies
View Related
Mar 27, 2014
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
[Code] .....
View 2 Replies
View Related
Jul 3, 2014
I'm trying to program a spreadsheet for staff attendance at a school. In the spreadsheet the staff member has personal days, sick time, and sick time in the bank (sick time rolled over from last year). When they use sick time, it needs to come out of the bank first, until it reaches zero, then it should move on to the sick time accumulated for this year.
Staff attendance WIP.xlsx
On the left side is the staff name, sick time, personal days, and bank. Then are the school days for July (if a staff member uses sick time/personal day it is placed under the date it was used). On the far right is the total remaining amount that the staff member has left.
The formula I have for cell AF4 is: =MAX(0,G4-SUM(H4:AC4)) so that the cell will not go below zero.
The formula I have for cell AG4 is: =E4-(COUNTIF(H4:AC4,"P")+(COUNTIF(H4:AC4,"HP")/2)) So that when P for personal day is entered, it will take a whole day, and when HP is entered a half a personal day is subtracted.
I need to figure out a formula for cell AD4. I've tried a couple of formulas I've seen on other posts, but they weren't for a series of numbers like this. And when I put them into this particular spreadsheet, it would subtract the bank until zero, move on to the 'sick time' cell and subtract the SUM of all the days sick rather than subtracting what was left when the 'Bank' reached zero.
View 1 Replies
View Related
Nov 15, 2008
The series 1,3,6,10,15,21,28,36 ("A" series) is the ongoing sum of 1+2+3+4+5+6+7+8 ("integers") and naturally goes on for as long as on chooses. I am only interested in the numbers up to 100.
Is there an Excel function that will allow me choose any integer as a starting point and any higher integer as an ending point and provide the sum of the "A" series associated with the two choices.
For example: If I begin at 6 and end on 12 it would give me 308 =28+36+45+55+66+78
00
11
23
36
410
515
621
728
836
945
1055
1166
1278
1391
View 9 Replies
View Related
Jun 16, 2009
I created a macro on an Excel spreadsheet:
Sub Macro1()
Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57
End Sub
When the macro is run, it works with the exact amount of data that I've entered.
However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)
How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?
View 9 Replies
View Related
Sep 4, 2012
How to create a code formula to calculate the arc length from a given chord length?
If you know the radius of the major circle.
Say the chord is 50mm and major circle dia is 72mm (radius 36mm)
arc from chord.jpg
View 5 Replies
View Related
Dec 10, 2013
I have a list of data in Column A of a spreadsheet that is all at different lengths.
I would like to return the numbers from this (see below) seperately and also everything after the last number so returning "green"
Example:
EAST MIDS, YORKSHIRE0112012green
EAST MIDS, YORKSHIRE0112012red
EAST MIDS, YORKSHIRE0112013black
EAST MIDS, YORKSHIRE0112013brown
View 1 Replies
View Related
Dec 4, 2012
How can I subtract a series of numbers from an array. example.
Code:
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
13
14
25
26
37
39
[Code] ......
=ABS(1-B2) C2,D2,E2,F2 AND G and after start again with =abs(2-B2:G2)
I want to know how to do this for example until 60, I mean 60 minus the row B:G
View 1 Replies
View Related
Aug 30, 2013
I have a graph with something like 40 series that are being plotted. For one, I create non-standard data labels using VBA. In that code, I refer to ChartObjects(1).Chart.SeriesCollection(34). At some point I deleted a series that preceded it in the "select data" list and the code crashed. Took a lot of digging to learn that SeriesCollection(34) had become SeriesCollection(33).
To avoid this in the future, is there a way to refer to the SeriesCollection by a unique name rather than a sequential number?
View 4 Replies
View Related
Apr 5, 2014
I have a series of raws with 7 numbers in each raw; sums may vary a lot from raw to raw, and also differences between one number and the other in a same raw.
Now, i need to obtain a much simpler distribution of scores, so reducing differences.
I would need to transform all numbers into a 0-4 score distribution, according to differences between numbers in a same raw. It's like a kind of rank score.
I want to obtain in each raw:
- 1-2 numbers with score = 4 (if three or more numbers have similar values: no 4 scores)
- a maximum of 3-4 numbers with score 2-3 (according to differences between these numbers but also also according to the 4 scores already assigned)
- all other scores = 1 if the numbers are >0
- all other scores = 0 if the numbers are 0
example: |14|23|3|0|45|0|10| => |2|3|1|0|4|0|2|
Would it be possible to do with Excel?
View 9 Replies
View Related
Mar 25, 2004
I have a spreadsheet, with about 1,500 lines. In column A is a list of numbers that I need to remove the last digit from each number, for example in A22 is 02602726521 - I need this to be 0260272652. The numbers are all uniqe.
How can I remove the last digit from every number, without going in manually to do so?
View 9 Replies
View Related
Jun 12, 2014
Have Series 1 (to be a line chart) with 20 data points X values are 0 to 68. Have Series 2 (Bar chart) with 68 data points, again X values 0 to 68
Cant get series 1 to display beyond X value of 20
View 3 Replies
View Related
May 27, 2014
I have a series of numbers in cells B2 to G2. The maximum value of each of these columns is found in B5 to G5. Is there a formula that will calculate the percentage of each of the values in row 2 (B2 to G2) and tell me which is the lowest number based on the percentage? Example...
Row 2: 12, 100, 43, 1444, 141, 332
Row 5: 25, 140, 55, 3000, 244, 440
Which number in row 2, is the lowest percentage of the total possible points found in row 5?
B2 is 45% (12 divided by 25), C2 is 71%, D2 is 78%, E2 is 48%, F2 is 58%, G2 is 75%.
I want a formula to tell me in a new cell that "12" (in B2) is the lowest percentage in that series.
View 4 Replies
View Related
Dec 18, 2011
am looking for a code that uses for loops of any other type of loops to create an array shown below:
1 2 6
6 5 1
2 3 7
7 6 2
3 4 8
8 7 3
5 6 10
10 9 5
6 7 11
11 10 6
7 8 12
12 11 7
The array above represents the node number of the triangles within a rectangle as shown in the image. [URL] .....
View 8 Replies
View Related
Mar 28, 2012
I am doing a Golf scorecard but I have been having trouble figuring out how to calculate the last 6 lowest scores in a series of about 30 scores. What formula to use? Series looks like this
43 52 52 51 44 44 60 54 40 50 53 = 272
View 8 Replies
View Related
Jul 18, 2013
What is the syntax for the formula for the minimum number, greater than zero, in a series of numbers.
I have a row of numbers, including some zeros, but I want the minimum number excluding the zeros.
View 5 Replies
View Related
Apr 4, 2008
I have a long list of numbers (1 to around 3000) in Column A and I need to print out only the numbers missing from that list. The numbers are sequential (except the ones missing) and there are no blank or missing cells, only missing numbers.
EXAMPLE:
1
2
4
5
7
.....
So I would only need to print the number 3 and 6. - it can be all in one cell, or in one column, it doesnt matter.
View 2 Replies
View Related
Apr 29, 2009
I have a series of numbers in one column. Is there some way I can divide all the numbers together by, say 10, without doing it manually?
eg.
12
15
15
25
20
should become
1.2
1.5
1.5
2.5
2.0
Is there a way in Excel to do that?
View 3 Replies
View Related
