Specific Find Followed By Equation Applied And Outputted To A New Column
Mar 23, 2007
I have the data sheet, which is the result of another function. It produces 10 columns with numbers in each cell, there can be any number of rows, but the key is that the rows are outlined as follows. The first row has the first column number bolded, followed by the second row not bolded and then a space and a repeat of the bold cell, followed by the non bold cell. Each row fills up all ten columns. I have attached an example of the sheet.
What I would like to do is create a function which says brings up a box that says "Input Row" and the user would input the bolded number of a row. From here the code would take that value entered and divide it by the inputted bolded number’s adjacent cell, to the right, aka B2 column. This value would then be multiplied by every other bolded rows second cell, B2 column, essentially giving rise to a normalization of that column. The output would then be displayed in a new column, column 11 in italics. Each row would now have its italics value corresponding to the bolded number’s row and not the other rows.
View 7 Replies
ADVERTISEMENT
Feb 4, 2014
Sheet one contains a table of table from C6:AY7000
On Sheet 2, I want to create a summary of data from Sheet 1 based on 2 Criteria which applies to column I and N.
On Sheet 1, I have put in a formula to link to sheet 2 for the criteria to make things easier.
So BN3 = Sheet2C3
And BN4= Sheet2C4
I have managed to run my coding to filter the data that I correctly want to copy and paste onto sheet 2 - however I only want to copy specific columns.
This is the coding I have written to select and filter the correct data values.
Sub AddFilter()
'
' AddFilter Macro
'
Dim rCrit1 As Range, rCrit2 As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
[Code] .......
This now gives me the correct data in the table.
I want to copy the visible cells from this table but only for columns e, dr, r, v, w, o, z, AD, AG, AQ, AW, AY
The copied data will then be pasted into sheet 2 starting in cells B9
I plan to attach a button on sheet 2, so when the user updates the values in C3 and C4, this will update values in BN3 and BN4 on sheet1, they can run my macro which will go to sheet 1, filter the data on what they have entered and paste the correct values.
View 2 Replies
View Related
Mar 8, 2014
I have the following 2 formulas that determine the top 2 scores and return the respective headers, but they aren't perfect.
=INDEX(T1:V2,1,MATCH(MAX(T2:V2),T2:V2,0))
The above returns the max, however doesn't take into account scores that are a tie or 0 values
=INDEX(T1:V2,1,MATCH(LARGE(T2:V2,2),T2:V2,0))
The above returns the 2nd highest value, but some instances the output is correct (if there is a tie or a 0).
How can I fix both of these equations and also determine the 3rd highest score?
View 5 Replies
View Related
Jan 3, 2008
refine this piece of code so it only looks in column 2 for a matching value.
On Error Resume Next
If S = "" Then
MsgBox "Delete Cancelled"
Else
Set r = .Columns(2). Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
On Error Goto 0
If Not r Is Nothing Then Application.Goto r, True
If r Is Nothing Then
MsgBox "That APL ref does not exist"
Else
Dim rowNum As Long: Rem *** *****
rowNum = r.Row: Rem *** *****
Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Select
Selection.Delete Shift:=xlUp
View 4 Replies
View Related
Mar 18, 2008
In my worksheet I have a column in which the cells have two possible values: "yes" or "no". In the first positions of the column there are only "yes" values, after there are some "no" values and then the "yes" values come again. Is there any function that identificates the position of the first "no" value in the column? And also the last "no" value? Attached I send a simple excel worksheet
View 2 Replies
View Related
Nov 12, 2008
I want to do is a simple list, 2 items, call them "Thing_1" and "Thing_2". I have an equation in a column of cells. The equation is a simple If statement. If the user selects "Thing_1" then the first equation will run, and if "Thing_2" is selected then the second equation will be used. That's it. No more complex than that.
My original questions about how to initialize a ComboBox are cross-posted here: http://www.excelforum.com/excel-prog...ml#post1996135
View 4 Replies
View Related
Jun 3, 2009
I have a column 'AE' that contains the html code for ecommerce item descriptions. While importing/exporting back and forth with another program and my spreadsheet, the following was added all throughout the descriptions.
%0d%0a
In some places, it may be three or four times consecutively
%0d%0a%0d%0a%0d%0a%0d%0a
Is there a macro script I could run to simply remove this any time it occurs in this column? It is just showing up as text and I am sure in another program it was representing something other than text, probably a space or hard return. Regardless, I don't need it.
See example below: ....
View 8 Replies
View Related
Mar 10, 2009
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button.
Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
View 7 Replies
View Related
May 7, 2008
I have a column with values like this
SW SW SW CO CO PD MDM
I use this line of code to determine the text
Set rFound = Sheets("ProductCount").Rows(14). Find(What:=sProduct, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
ProdRow = rFound.Column
where sProduct contains values such as SW , CO. Now, when I use rFound to query SW, it finde me this first occuring SW. I have this inside a for loop. When it goes and queries again, it should get me the next occuring SW, not the first SW that it searched for me.
View 3 Replies
View Related
Feb 14, 2010
In the attached sheet I need to check on the Destination in column B, and if it matches set criteria, divide the figure in Column E by 2. eg If LAX is the destination, then for any rows directly under LAX until the next destination appears, column E needs to equal column D divided by 2. If the Destination is not LAX, then Column E equals Column D.
the difficulty I am having is that there can be a variety of row numbers for each destination. I am having trouble creating a formula that can basically work backwards from Row-1 in column B until it gets to a text string, and check that text string against the criteria. so it may be row -1 up to about row -9 dependant on the number of flight times available to each destination throughout the week.
View 5 Replies
View Related
Oct 1, 2012
I have an excel workbook where some sheets have a column called "Name" in Row 3. The column where "Name" appears shifts based upon other criteria so it's not set within 1 specific column.
The real data for the "Name" column starts in Row 5. Is it possible to create a macro that looks in Row 3 for "Name", then once it finds that column, it does a find and replace from Row 5 to the end of the data and replaces every space with a ^.
For example, if I have:
Row 3 Name
Row 4
Row 5 John Smith
Row 6 Jane Doe
Row 7 John Doe
Row 8 Jane Smith
I'd like the data to become:
Row 3 Name
Row 4
Row 5 John^Smith
Row 6 Jane^Doe
Row 7 John^Doe
Row 8 Jane^Smith
I'm using Excel 2010 if that impacts anything.
View 1 Replies
View Related
Mar 27, 2014
I have the table, as shown on left in the appended image, and the final result should be the right one.
The steps are written as 1, 2, 3 and 4.
Untitled.png
View 13 Replies
View Related
Aug 26, 2008
I am not the best at this, but we recorded a macro and we want to delete whole rows after we 'control' find something from a specific column. WHen we recorded our macro, it finds the first instance and we hightlight the whole row and 'control' 'shift' 'end' and delete all of the rows. We did this because we sorted and made sure the information we wanted to delete was at the bottom of the worksheet. we realized that each day the spreadsheet we pull from an ip address gets bigger and the row that we started from to delete starts on a different row each day. How do we get all the information to delete everyday, even when the row that Owned starts on changes? ...
View 9 Replies
View Related
Dec 30, 2011
how to work macros or VBA
I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:
Column B = due date
Column D = loan# A
Column E = loan# B
Column F = status
Column H = followup needed
(Columns A,C, and G aren't important for the current need)
What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.
I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.
The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).
I already have the tracker sheet set up and ready to go with the spaces as follows:
Column G&H = Merged cells where due date will need to go
Column I&J = Merged cells where loan# A will need to go
Column K&L = Merged cells where loan# B will need to go
Column M thru S = Merged cells where followup needed will need to go
View 9 Replies
View Related
Jun 19, 2014
I have data that is on a separate .txt file (the source file) that resembles this;
A
B
C
D
E
F
1
Case
District
Pct
Division
Level1
Level2
2
305035
0
20
72
[Code] ..........
Using VBA from within the destination workbook, I would like to use an input box that allows the user to request/specify what numerical number (0 through 55) in Column B of the txt file (the source file) will be used to determine what data to copy to the destination file. The cells to copy to the destination file would be from Column A through Column G of the source file onto Sheet1, E2 of the destination file.
Column B of the Source file will only contain numbers. However there will be numerous identical numbers in Column B. All of which will need to be copied onto the destination workbook.
From the sample above, if the user were to enter '15' in the input box, the desired result on the destination sheet beginning at E2 would look like this;
E
F
G
H
I
J
1
Case
District
Pct
Division
Level1
Level2
2
502046
15
1
3
[Code] ............
View 9 Replies
View Related
Apr 25, 2013
I have a macro that converts all my PDF Purchase Orders to a text file and inserts the data/text horizontally into another document. However because the PDF's or the text within the PDF can be fomatted differently (that is on different lines etc) it therefore imports the information and it looks mis-aligned.I have attached a simple spreadsheet showing some sample text as it is imported and then below this how it should look like, all in line.
The range where the highlighted text in red is, is variable (but say nothing more than a variant of 10 columns). The text can also be Uppercase or Lowercase.So, I was wondering if there is macro code to find the "text" on various rows/columns and align it all in another column?
View 2 Replies
View Related
Jun 14, 2013
I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken
Cow
Donkey
Pig
[Code]....
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
View 3 Replies
View Related
Mar 14, 2014
I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
View 5 Replies
View Related
May 27, 2014
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.
View 3 Replies
View Related
Aug 5, 2013
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies
View Related
Jun 18, 2014
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C
Notice the two examples in the excel file.
Excelforum.xlsx‎
View 6 Replies
View Related
Dec 10, 2007
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
View 5 Replies
View Related
Jun 21, 2006
I need a code that will delete a row if it has a specific text in a specific column.
In this case in column W if there is anything that starts with '>TC' I need that entire row deleted that its in.
View 7 Replies
View Related
Jun 23, 2008
I cannot acheive to have a {Sum(if(...)} formula applied to whole columns. See below.
The following formula return a numeric value. So it works.
{=SUM(IF(Query!$L1:$L65535=TEXT($A37,"@"),Query!$AM1:$AM65535,))}
(confirmed with Ctrl+Shift+Enter)
The following formula returns #NUM. So it fails.
{=SUM(IF(Query!L:L=TEXT($A37,"@"),Query!AM:AM,))}
(confirmed with Ctrl+Shift+Enter)
how to use {Sum(if(... )} to whole columns?
View 9 Replies
View Related
Apr 22, 2009
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies
View Related
May 10, 2007
I have a very simple workbook in Excel 2000 with one page containing all my data which I update on a daily basis. Some of the data is linked to another workbook so that it updates each time I open it. Then I have 12 tabs, one worksheet for each month of the year with 10 simple graphs on each worksheet.
As you can see, it is a very simple worksheet. Each month, I copy and paste the graphs from the previous month into the current month and simply alter the Source Data in the graph to gather the current month's data. At the start of this month, it allowed me to copy and paste, but when I tried to alter the Source Data to the current month, I got a window on screen with a yellow triangle containing an exclamation mark and the words "No new fonts may be applied to this worksheet". If I click the OK button or the cross, it responds but the whole programme is locked. The only way I can get out is to give the 3 fingered salute and close Excel down.
View 2 Replies
View Related
Oct 10, 2007
I have a workbook with 33 worksheets and more than 150 charts. Now, when I right-click on a chart to access "Source Data" for example, I get a message saying "No more new fonts may be applied in this workbook". Then I have to click "OK" about a dozen times before I get access to the dialog box. Curiously, it happens for some charts and not for some others.
View 7 Replies
View Related
May 14, 2009
If filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.
View 2 Replies
View Related
Mar 6, 2013
I have a auto filtered applied to 1000 rows of data and the actual quantity of rows I currently see is 55 rows. When I do a filldown command to apply this formula, "=CONCATENATE(X$1,I51)" the result I get is wrong becuase where the "I51" value is at the formula is picking up cell values that are hidden due to the applied autofilter.
What do I need to add to this formula so that it only picks up cell values that are not suppressed by the autofilter? I want it to disregard the hidden rows when I do a filldown and only apply the formula to the rows that are not hidden due to the applied autofilter.
View 1 Replies
View Related
Mar 28, 2014
How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"
View 5 Replies
View Related