Extending Range Function To Account For Different Row Lengths?
Apr 25, 2014
VB recorder in Excel. The macro is designed to process a spreadsheet that will have the same number of columns and same type of data in each column, process this data with a formula (hat tip: AlKey+XOR LX) and then clean up the columns ready for use in another program.
I have got it working but when I alter the row length the macro only works to the final row in the recording spreadsheet. I realize this is because when recorded the macro I told VB to set the range to a given row.
I remember there is a way of setting the Range function that tells VB to always go to the bottom of the rows filled with data but I can't find it for the life of me.
see the attached file the training spreadsheet.
[Code] ......
DdSample_training_data.xlsx
process.docx
View 4 Replies
ADVERTISEMENT
Sep 7, 2012
Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)
[Code] .........
View 1 Replies
View Related
Jul 27, 2007
I have a formula IF(A2=A1+1;B1+1;1) that I need to develop further, so it can recognise same date for same patient, so it doesn't start back at 1.
e.g. ( see attachment) In column B are real dates( 01.09.10-01.09.12, trough my formula => day 1,2, 3). What I need help with is to make the formula linked to column C so that if there are more of same date in column B( because of several different analyzes taken same day of the patient-column E) fore example several 09.10.11 should be recognized as day 2 all of them for the same patient, and if a new date follows for a new patient it should start back at day 1.
This is so I can make a diagram of my analyzes taken at the ICU, the problem is that my research is during 3 years and because of that I have different dates of analyze, and I need them to be comparable so that the day that they arrive changes to day 1 and so on.
PS! I have made an example were I have changed the patients IDs and taken away unnecessary data.
View 11 Replies
View Related
Dec 7, 2006
If I have a sum function that adds up:
= SUM(U41:U45)
but I add a row at 41 I now get sum function
=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:
=SUM(U41:U46)
View 3 Replies
View Related
Oct 19, 2012
I imported my data and I have a macro that updates the data. Nothing new until new items were added, creating new rows at the bottom and now the Table Format does not include these rows. There are so many formats available that I don't want to guess which one I used when I created it I just want to extend the data range.
I do not care if this is done through the macro or right on the sheet as this does not happen that often.
View 3 Replies
View Related
Nov 24, 2009
I'm having a problem with a dynamic range. I've re-checked my formula at least 10X and its syntax is correct. The problem is the dynamic range stops short 3 rows up from the last entry.
View 9 Replies
View Related
Aug 4, 2014
Goal: To extract the barcode (numbers and letters between B/C: and , P/G) from the following string:
"Codec B/C: A01234567, P/G: 123456728912345"
Current Function: =MID(A1,(FIND("B/C:",A1)+5),(FIND("P/G:",A1)-14))
Problem: I need this function to work for barcodes of varying lengths. For example, in some cases the barcode may be 8 characters long and at other times the barcode will be 14 characters long etc.Below is a screenshot of how I have this currently set up in excel.
View 7 Replies
View Related
Aug 21, 2008
I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.
For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel ......
View 9 Replies
View Related
Apr 20, 2009
I am trying to use a Mid function to help me in my vba problem.
In my first table I have a list of account numbers. E.g. 'ZZ500543'
In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath'
I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?
View 14 Replies
View Related
Aug 21, 2009
I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?
View 9 Replies
View Related
Apr 6, 2013
I have account numbers in Col F and values H on sheet1
I want to set up a formula on sheet account classification to add the values in Col H based on a range of account numbers from 3000 to 3017.
View 6 Replies
View Related
Jan 6, 2014
Need formula to find a specific account no. from a range of text and return that account's value as a usable numeric.
Account number:012345678 XZ
Account value as of 12/31/2013:$12,345,56
Account number:987654321
Account value as of 12/31/2013:$876,543.21
View 12 Replies
View Related
Apr 2, 2013
I would like a formula to add up values in Col E based on a range of account numbers in Col C
For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.
View 9 Replies
View Related
May 8, 2007
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
View 17 Replies
View Related
Jan 13, 2014
How can I extend a table backwards my table starts from column B and i want to make it from column A to make it start from column A that is, if its possible.
I have uploaded my workbook : Stock Request - Copy.xlsm
View 1 Replies
View Related
Feb 15, 2010
Using the oldest version of Excel (200) I have to insure all members of our squad can utilize this function. Trying to format a whole row based on one cell in that row that reports number of days till the next physical. My formatting works correctly for the initial cell, but does not extend correctly to the rest of the row. I am using the "Cell value is" option. I used:
Cell value is between 1 and 365 - color green
Cell value is between 0 and -30 - Color Yellow
Cell value is between -31 and -365 Color Red.
All cells with no value in it are yellow, even if the original formatted cell is another color and the cells that have other data in them such as the physical date, have no color in them regardless. read and tried several approaches from other threads with no good results. considering the results from those threads, I am curious if it has to do with it being an older version.
View 3 Replies
View Related
Dec 22, 2009
i have a calendar with the dates in a row
I want to count instances of an entry under the dates using 'countif' from 01JAN until today.
My problem is that today changes value [up by one] each day so the 'countif' function will need to extend its range to the right by one cell each day.
View 11 Replies
View Related
Jan 30, 2010
use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution:
http://www.excelforum.com/excel-gene...-new-post.html
I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
View 2 Replies
View Related
Jul 8, 2009
I want to extend a formula like so- =Sheet2!M3
=Sheet2!M60
=Sheet2!M117
Basically I want it to go up in increments of 57 when I copy the formula down. Is there an easier way to do this rather than typing it over and over again? I looked on an older post and saw some information about OFFSET and INDEX but couldn't figure out exactly how that worked.
View 4 Replies
View Related
Nov 14, 2008
I work in Local Government and I am recording the contact that we receive for each transaction that are avoidable or unavoidable. This is broken down into service areas.
The data is imported into excel from a database and therefore is constantly growing.
What I want to do it a very basic summary page so that I can group those that are and those that aren't avoidable for each service.
Because the data is ever expanding I would like this on a separate sheet from the actual data.
I attach a sample of the worksheet and would be grateful if someone could advise what formula to use on sheet 2 in cells c4, c5, d4 and d5 to make it do it automatically.
View 5 Replies
View Related
Nov 18, 2008
I have a list (external), which will be updated every month from an external source with new numbers and possibly a new size. This will be a count of services people use. On a 2nd sheet I need to allocate a cost to these services (pivotdata). This works fine if the list stays the same size, but it most probably won't so I need it to update automatically, not manually extending the list on the 2nd sheet. In brackets are the named ranges of the data in the spreadsheet.
View 5 Replies
View Related
Feb 9, 2009
However I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work
unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines
View 5 Replies
View Related
Nov 16, 2013
This is what I am using:
=VLOOKUP($A5,TEAM!$B:$MZ,23,FALSE)
When I select the cell formula extension button thing and drag it to the right, the formula copies exactly into the next cell. If I select multiple cells starting from 19-23 and then drag, excel just repeats the formula over, (19, 20, 21, 22, 23, 19, 20, 21, 22, 23, 19, 20...) This is seriously frustrating as I have to click into the formula bar on each cell and change the value of the column lookup.
I can't keep going manually as I need to go to about 300 cells...
View 9 Replies
View Related
May 20, 2009
I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)
I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....
Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable
Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
View 14 Replies
View Related
Aug 18, 2009
I have 4 lengths in four columns in a random order, and need to compare them to see whether they are equal lengths.
I Have figured out how to order them so I can compare them, but can't think of a formula to show whether they are equivilent (eg 1000m = 1km) True or False outcome is fine.
View 3 Replies
View Related
Oct 15, 2009
I have three columns, one column is time every two seconds with data associated that time, and one is time every minute with an associated tidal height.
I want to sort my data so that for every 2 seconds I have an associated tidal height for that minute.
eg:
what I have:
Time Time Tidal Height
(2 sec intervals) (min) (meters)
1:15:00 1:15 1.342
1:15:02 1:16 1.221
1:15:04 1:17 1.115
...
1:15:58 1:43 0.024
1:15:60 1:44 0.012
1:16:00 1:45 0.008 ....................
View 2 Replies
View Related
Aug 19, 2014
I have a performance extract from a system which has performance for multiple accounts but the data is off different lengths and shown vertically. I wish to move the data onto a new sheet but rotated(transposed) horizontally
Attached is a snapshot of the report that is usually about 20,000 lines. See input and output tabs.
Is there any way that a macro could be created to do this.
Performance.xlsx
View 3 Replies
View Related
Jan 16, 2006
My data is as follows, all in one cell:
10015200 US Government Bonds 18,369,423.52
10012301 US Government Bonds 10,232.21
There are many varying lengths of digits. I am trying split off the numbers
to the right of US Government Bonds into another column. Here is my
formula:
=RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7)))
My result is 9,423.52. Its not pulling the whole number.
What do I need to change in the formula?
View 14 Replies
View Related
Feb 28, 2009
Say I am downloading a column of stock prices and it cannot be predicted precisely how many rows will be contained in each downloaded column.
The column is, however, always Column E.
Two formulas must be computed.
1) Last row with a price in column E / average (all rows, beginning with row 2 with prices in column E)
2) Last row with a price in column E / average (last 50 rows with prices in column E)
Can Excel manage the variable number of rows required for these formula, and if so, can somebody recommend how to write these formulae?
View 11 Replies
View Related
Jan 3, 2008
Is there a formula out there that can check if a cell begins with 1 of a range of numbers of different lengths?
Example does A1 begin with A2, A3, A4, A5 where A2 = 123, A3 = 4567, C3 = 89.
View 9 Replies
View Related
