I have a list of dates in column A (sequential from low to high).
I have corresponding rainfall data in column S.
I am trying to find the longest run of 0's in the rainfall data and return the start and end dates. It would be great if i could tell it (in cell C8) to find the longest run below this number.
ie: i say i want to find the longest run of numbers below 5.
I am trying to figure out how to create a formula or VBA to count how many contiguous 0's there are in a specific row...and then drag this formula down many rows..for example - if a row of data contains 1,3,0,0,0,4,5,0,0,0,0,0,0,0,5,3,1,0 I want the result to be 7, because 7 is the longest streak of contiguous 0's.
View 9 Replies View RelatedI have a database application which appends an "A" to the sequence number each time an entry is amended and then posts it as a new row in the database. This means there can be multiple entries with the same number but with different amount of "A"'s afterwards.
e.g.
Cell A6 contains seq no 1
Cell A7 contains seq no 2 - Seq no 1 is subsequently amended so...
Cell A8 contains seq no 1A - 1A is subsequently amended so...
Cell A9 contains seq no 1AA - and so on and so forth..........
What i am trying to achieve is a formula which will look at the record number which is required (i.e. 1) and will search for record 1 with the most number of "A"'s appended. This way the user will only be amending the most up to date record.
The formula i have tried (but doesn't work) is:
=INDEX(A6:A65536,MATCH(B2&MAX(LEN(A6:A65536)),A6:A65536,1),0)
(B2 is the record the user wants to amend)
How I can show the longest string in a column, I've tried to find a formula that does this but it only shows the number of characters, not the actual string contents.
View 2 Replies View RelatedI had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:
Dim sNm As String, sRT As String
If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->
sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& Target.Address _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"
ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT
My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.
I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
View 4 Replies View RelatedI have a range of values, (1,10,20,0,2,5)
I need to find the minimum values, but I want to avoid 0(zero)
In Column A I have numbers ranging from 0 to 500, I want to consolidate these in column B or C to pull through all numbers >0.
I have a formula but its pulling through everything except for the blanks:
=IFERROR(INDEX($A$2:$A$1700,SMALL(IF(ISNUMBER($A$2:$A$700),ROW($A$1:$A$699),""),ROW(A8))),"")
how i can amend my formula or create a new one?
How can I get the Longest sequence of 3's. E.g.
CA
1
2
3
5
3
3
5
4
Would like to Condense Column Q to Col S no Blanks no Zero's
Tried formulas on net but won't work for me ? ?
Column A contains numbers 0, 2, 0, 0, 5, 6, 0, 8 (in cells A1 - A8 respectively)
Can I write a formula to make column B contain only the non-zero numbers (2, 5, 6, 8 in cells B1 - B4 respectively)?
I'm attempting to summarise several hundred control charts.
One thing I'd like to do is be able to put in a formula to count the maximum number of successive entries that are all the same side of the mean.
Another related thing is to be able to count the longest run where successive values are the same.
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.
What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.
Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.
I am looking for a formula that returns the date of the first and last value in the longest continued range.
In the example:
CD30CLA3101/02/200833201/03/20086
3301/04/200853401/05/20083501/06/200863601/07/200823701/08/2008
The two formula should return the two dates in blue.
I'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 need a formula to count the zero's from a column and to put the value next to the 1 and when another 1 appears after a 1, it has to show 0, like in the example bellow.
example1.xlsx
I am looking for a way to ensure that all the leading zeros in one column are present without having to format the column cell be cell. Is this possible?
I've worked on this one way to long and thought I'd ask here now.
Column A Column B
A 1
A 0
A 0
B Blank Cell
B 0
B Blank Cell
B 0
B 0
I have a third cell that I would like to count how many zeros in Column B there are that correspond to the letter B in Column A. Blank cells cannot be counted. This answer should be 3.
I analyze logged data that often contain ZEROS in column L, always starting on row 35. That's bad data. The first row with good data contains the number 700 in column L, but this row number is unpredictable.
How to make a VBA code to exclude the rows that contain the number 0 in column L, searching between cell L35 (including) all the way to the first row containing the number 700?
I need to limit the range to be scanned for ZEROS because sometimes there are valid ZEROS in column L, but those would be in rows below the rows contain 700 in column L.
I am trying to do the following with VBA.
What i need is to find out the last cell with data and then selecting the whole range and copying it.
Please see the below.
As you can see, the column with the "longest" data range is B9,C9,D9,E9.
I need a VBA code to detect which Column has the longest Data and from there copy the entire range.
Hence, in this case, the range to be copied is From A2:J9.
Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J
1
2
ttt
rrr
m
vvv
gg
ff
fff
fff
fff
[Code] ..........
Hence,in this case, the range to be copied is From A2 to J14.
Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J
1
2
hjhjh
ghj
gh
ghj
ghj
ghj
[Code] ..........
I’ve created a formula for this statistic and I’m happy with the results. Because I’m working with formulas, my only problem is the unwanted zeros. How do I hide zeros that show up automatically (i.e. #3 [blank] and Nov 09-June 10)? I can hide the numbers, but if I enter a zero to one of my future statistics it will not appear and I don’t want that to happen. Is there a way to hide those automatic zeros without affecting my real zeros?
Vendor’s Name
Jul 09
Aug 09
Sep 09
Oct 09
Nov 09
Dec 09
Jan 10
Feb 10
Mar 10
Apr 10
May 10
June 10
1
Vendor1
20
5
15
3
0
0
0
0
So quick sample of data :
B74
B74
9
94
1
948
B74
So if I have this data in a column you notice they have different length. Now I want my macro to add leading zeros until the length of all occurrences is 4.
I know for numbers you can do a range.numberformat = "0000".
But this won't work for B74 because (obviously) it will not be recognized as a number.
I know in a formula you can do it with the command TEXT. However I do not want to create extra columns I want something to alter it in place just like the numberformat does but then for numbers and text.
I have a column of about 13,000 10 digit numbers. I need to add four zero to the beginning of each row of numbers so I can make it 14 digit numbers. Is there a way to do this without cutting and pasting four zero's for each row of numbers.
View 10 Replies View RelatedI have the following code
Call LastCell_Example(LastCell)
lastrow = Cells(Rows.Count, "B").End(xlUp).row
Set Datarange = ActiveSheet.Range("B1:" & LastCell)
' Set to 0 all cells that contain constants, but ONLY those that are numeric
Datarange.SpecialCells(xlCellTypeConstants, xlNumbers) = 0
lastcell_example contains
Dim rng As Range
' Use all cells on the sheet
Set rng = Sheets("Courtage").Cells
' Find the last cell
LastCell = Last(3, rng)
' Select from A1 till the last cell in Rng
With rng.Parent
.Select
.Range("A1", LastCell).Select
End With
When I run the code above, I don't understand the fact that the first rows in the sheet AFTER "Courtage" are also being changed to zero.
Obviously, I'm not limiting my datarange correctly, but could someone explain what I need to do to limit the changed data to ONLY the current sheet.
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.
0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities
0014B23<@28>Coursey<@28>Ogles<@28>Shelocta
Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B23<@28>Coursey
0014B23<@28>Ogles
0014B23<@28>Shelocta
0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches)
0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches)
0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)