I am trying to create a macro that will look for a pattern and if the cell has that pattern, then the cell next to it in the row will have a different pattern.
For instance:
for each row in the spreadsheet:
if column a = "Hello" then column b = "World"
if column a = "AAA" then column b ="BBB"
and so on, for different text values.
I need a macro that will insert a column next to column B, and insert formulas into the new respective cells. forumula needs to be in the macro, it won't be located somewhere else on the sheet (for simplicity's sake, let's just say the formula is =A1+B1, then A2+B2 in the next row, etc). Also, it needs to stop inserting formulas when there's no more data in column B.
View 1 Replies View RelatedI need a macro that will copy each formula down each column from row 2 individually and then copy & paste values for each column (from row 3 to row x) until it reaches the first column with a blank in row 2 column y.
1. Copy A2 to A3:A65536
then Copy A3:A65536
then Paste Values A3:A65536
2. Copy B2 to B3:B65536
then Copy B3:B65536
then Paste Values B3:B65536
3. Do to each column until first blank row 2 ( no formula)
I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN “Q” in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.
I want the Macro to do the following THREE THINGS:
1. IF the Value in COLUMN L is “0” THEN enter “n/a” into COLUMN “Q”
2. IF the Value in COLUMN L is “2” THEN enter “n/a” into COLUMN “Q”
3. IF the Value in COLUMN L is “1” THEN use a formula that looks like this:
= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a “2” in COLUMN L and then MULTIPLY that Result against those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN “Q”. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 – 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.
Here is an example of how the formula will work:
I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are “Times” and “PM” and contain a “2” in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN “Q” IS 10.395.
Here the DATA SET:
Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T
Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ
This is what is happening in Column Q Explanation of Formula
Row 2
Times
Cont
2
28 28 n/a
n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".
Row 3
Times
Cont
1
404 1194 413.4740369
O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:
1. The "Org" value of "Times" in Column C
2. The "S Function" value "Cont" in Column H
3. The "P ID" value "2" in Column L
[Code] ...........
What is the macro code to take a cell say H7 which has the formula =$K$3-F7, and filldown to the last row of data in column E? This needs to be dynamic as ill be running this from period to period so the number of rows will always change.
H7 will always be the starting cell for every period. Just not sure how to tell VBA to fill this formula down and stop at the last row of data with another columm as a reference.
I want my column to be populated by formula using VBA. Let's say on row1, the formula is = SUMIF(mysheet!$L:$L,$A1&C$1&"09",mysheet!$M:$M). And on row 2, the formula is
=SUMIF(mysheet!$L:$L,$A2&C$1&"09",mysheet!$M:$M). And on row 3, the formula is =SUMIF(mysheet!$L:$L,$A3&C$1&"09",mysheet!$M:$M). And this is done up to the last row. How do I do this?
Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed
Disposal Order
Latest Decision date for D.O.
06/05/2014
D.O.001
[Code] ........
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
I am trying to write a formula where the column header of the row in which a value other than 0 exists, will display for each instance (row) where a value exists in an array spanning 3 columns. So the result cell could be any of the three column headers, or a combination thereof.
I started the formula in P2 of the GL Detail-2012 tab. File attached.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,))). Not working.
I have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F
and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
[Code].....
I'm using Excel 2013.
see the attached spreadsheet including the data. In Column AA I have created a formula that looks at Column B, and pulls each unique value from that column. add to this formula, or propose a new one, that pulls unique values into column AA if at least one of the rows has an "Actual Finalization Date" in column D that is in 2014?
View 1 Replies View RelatedI've got a table of different values in column A.
I'd like the cell D2 ("order") to show up at column B next to the closest value of 35.00 of column A.
Consequently, how to make "order" meet the closest several value of 35.00?
I've attached a xlxs file with more detailed description.
Ex_closest_11_05.xlsx
How would I count unique dates associated with another specific value on a worksheet?
For instance, Lets say I want to count each day a particular person makes a sale.
Example:
Sales PersonSale IDSales Date
Don 1001/6/2014
Don 1401/7/2014
Don 1601/8/2014
Jack 1011/6/2014
Jack 1021/6/2014
Mike 901/5/2014
Mike 1031/6/2014
So on another worksheet, I'd have:
Sales Person Days with Sale
Don 3
Jack 1
Mike 2
The Formula would go into the "Days with Sale" Column.
I am creating a tool that is populating information off of another excel document and presenting the information in a different format then the data originally appears. Basically I am unsure how to create a formula, for example, in cell A1, that as I drag it down (A2, A3, etc) the column letter in the formula advances but the row number remains the same. In another words as the formula moves into cell A2, the "Sheet1!D3" changes to "Sheet1!E3", where as normally it would advance to "Sheet1!D4". I just started back up in excel, im sure this is way easier then I am making it seem but I have been unable to come up with a solution.
View 4 Replies View RelatedI need a formula to run down a column DCapture.JPG (starting at 142), when it finds the last entered value it needs to display the corresponding value from column J into cell AA21.
If you see the attached photo, the last entered data in column D would be 1, AA21 would be saying -30 (J205)
Capture.JPG
I have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A.
Here is what I have but it is counting the dates in col A not the order numbers in B?
I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.
View 3 Replies View RelatedI need a macro to move specific information.
I get an excel sheet with W/C Employer: (whatever) entered below the patient it belongs to in column E. This information needs to move into its own column (H) in line with the above patient information so I can use it in a mail merge to word.
The same thing needs to happen with WC injury date listed in column G. It moves to column I. Then delete the row it was moved from so I don't have an empty row.
Sheet 1 is what I get. Sheet 2 is what I need to end up with.
I want to insert a formula that is tied to a specific column. I know how to do formulas and have a slight understanding of macros. Can I insert the formula as part of a larger macro?
View 3 Replies View RelatedI have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation.
For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?
I am running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
I am wanting to paste formula from N1 till N X (X = varaiable row) where X reliant on Column A Row X. If Column A Row X has character "=====" it should paste N1 Formula all the way down till N Row X which is equal to A Row X containing "=====" .
I might be sounding complicated over here but it is a simple equation.. I have tried to approach this in the capacity i could by condition if Column A row x is blank delete the row.
I need a formula to return column header if there is data in the column, any data.But I also need to return the second, third, fourth header with data too
In column A row 2, I have =IF(M8"",$M$1,IF(N8"",$N$1,IF(O8"",$O$1,IF(P8"",$P$1,IF(Q8"",$Q$1,IF(R8"",$R$1,IF(S8"",$S$1,IF(T8"",$T$1,""))))))))
This returns the row 1 header for the first instance of data in row 2 columns M thru T But in B2, I need the second header with data in the column.
I need the results in yellow (1st, 2nd, 3rd, 4th) from the array to it's left
1St match2nd 3rd 4thColumn MColumn NColumn OColumn PColumn QColumn R
Column MColumn PColumn QData data2Data3
Column NColumn OColumn PColumn R Data Data2 data3 data4
Would use match/index but can't increment it to the second or 3rd match
I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1
Month value specified in: G1
A
B
1
Name
Action
2
Joe
Created
3
Bob
Approved
4
Cindy
5
Jane
6
Dave
7
On 18 worksheets I intend to use variations of the formula below to read ColsD:G and ColsK:N to ascertain whether the values are less then or equal (
View 9 Replies View Relatedmy existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
I have a spreadsheet which is updated daily. Row A of the sheet has the date in it, and every day a new column is created for the that set of data. I have the below code which works at the moment:
[Code] .....
I want to use this same code on another spreadsheet to do the same process (I need to copy and paste 4 columns (A,B,C + D, into E, F, G & H, then tomorrow it will copy E, F, G & H into I, J, K &L etc etc)). The problem I'm having is that A1:C1 is a merged cell, then D isn't (used as a border to separate). So when it is copied I need to select the merged cell columns and column D (i.e. A:C & D on day 1) and paste it into E:H with E1:G1 merged.
a macro to convert this;
a
b
c
d
[Code]...
Into this;
x
a
9
x
b
[Code]...
So far I have the following, but this is not quite right!
[QUOTE][Sub ConvertRange()
Dim targetRowNumber As Long
targetRowNumber = Selection.Rows(Selection.Rows.Count).Row + 2
Dim col1 As Variant
[Code]...
/QUOTE]#
How would you set up a macro to delete numbers in a spreadsheet in one column that appear in another column or worksheet?
In other words, if I have the numbers 1,2,3,4,5,6,7,8,9, 10 in one column and in the same column, or another column or worksheet I have the numbers 1,2,3,4,5 - I need a way to delete 1,2,3,4,5. I know how to delete duplicates via the advanced filter but I want to have the duplicate and original deleted.
