Looking Back Through Cells In A Column To Find Specific Text(s)
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
ADVERTISEMENT
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
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
Apr 14, 2014
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 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
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
Apr 10, 2008
I want to search for a word in column A and when I find it I want to copy it to column B.
Column A is a description that can be 6 or 7 words long. Column B is a single word.
Example:
Col A
Engine Kit, V-8, 306, forged.
I need to copy the word Kit to column B.
View 9 Replies
View Related
Apr 26, 2007
For example
Name Address Phone # zip
Danielle 4561
Danielle 9852
Danielle 22
Danielle 69
Joe 895
Joe 28
John 9821
John 1114
John 698
Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?
View 11 Replies
View Related
Jun 25, 2014
Median Indirect: I would like to find the median in a range of cells and then bring back the 2 adjacent cells
Range...............Adj Cell 1............Adj Cell 2
1.......................L1......................L2
2.......................P3......................P4
3.......................O9......................010
So the median of the range is 2, thus we would want the formulas to bring back P3 and p4
Column 1..............Column 2
P3...........................P4
View 12 Replies
View Related
Jul 27, 2007
I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.
I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.
Here is my formula now:
=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)
J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.
View 9 Replies
View Related
Jul 13, 2012
I would like to search Column C for an instance of the text "Std. Residual", then cycle through the non blank cells to the right and run some formatting code:
VB:
'this line will be modified so that i cycle through the non blank cells that i have found
For row_cycle = 1 To 7
'not signifcant
If Abs(ActiveCell) < 1.96 Then
Selection.Interior.Color = 255
[Code] .....
I then want to search for the next instance of "Std. Residual" in Column C, do the same again, and so on for the entire Worksheet.
View 3 Replies
View Related
Mar 4, 2014
I have a worksheet with several columns. I need a formula to search column D only and each time a specific location is identified to replace that location with alternate text. Example (ORIGNAL TEXT):
Column Dtext to text help.xlsx
BIRD
FISH
DOG
BAT
BUG
I need to search that listing and each time the word BIRD is mentioned have it replaced with FEATHERS and each time DOG is listed have it replaced with TAILS Final result would look like:
FEATHERS
FISH
TAILS
BAT
BUG
All other text should stay the same and replacement text should appear in the cell of the text it is replacing. This is a sheet used by multiple people several times a day and so the Find/Replace option really won't work.
Have working on this for at least 6 months and it just isn't going to happen for me. I thought I could use a Conditional format, but that is producing no results either.
View 3 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
Jan 23, 2014
I want a formula to find "#" character in a text.
Example
PO# 4343434, MINCDSD.LTD, 977766
Here i I want to find out the character "#" position
I used =FIND("#", A1,1) Answer is 3 it's ok fine.
But I have problem with this below example;
Revised PO#545455, INV# 434344 ABC LTD
In this example i want second "#" position..
View 5 Replies
View Related
Feb 1, 2010
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
View 3 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
Jul 12, 2007
I tried to writer my code myself but I have a long way to go. Here is what I must do for my case:
I want a macro to find the row that include a specific text (For example "SMSC") in a range (A1:A100) then copy this entire row below the cell which has the value "OTHERS". That is, if there are 10 pieces of "SMSC" so these rows including "SMSC" should be listed below the cell "OTHERS"
View 9 Replies
View Related
Feb 3, 2014
So I have a column that will have the same text in all of the cells contained within it except for one cell. Is there a formula that I can use that will automatically find and transpose the text of the one cell that is unique out of the column? I was thinking of an IF function, but the column could be up to 60 cells in length.
View 1 Replies
View Related
May 28, 2014
formula to find specific text from statement.
I manage to use formula find for 1 text in 1 statement, but in confuse how to get another text found if i have "8 text" to find. Generally, there will only be "1 text" for each statement.
Maybe in 1 statement there have only "IC" or "Staff Tagging" or Name" or "Product" or etc till 8.
View 8 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 28, 2009
Could you tell me how I can find a specific sentence within a cell that contains many sentences.
for example
I want to find, "I am new." within a text that contains, "Hello I am Bob. I am new. I live in england."
I am currently using =+FIND(AB$1,$V2) where AB1 contains the sentence I am looking for and V2 contains the cell full of sentences. However this returns #VALUE! when the sentence is not found. I want it to return null.
View 5 Replies
View Related
Oct 15, 2012
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs
40m20secs
2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
View 9 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
Feb 13, 2010
How can I change the code to search for cells with specific .Interior.ColorIndex
View 2 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 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
View Related
Sep 13, 2010
I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.
View 9 Replies
View Related
Oct 18, 2013
I have a client list in a table in my excel sheet. I currently enter the clients in a sheet that looks similar to a form. After the clients info is in this Form I have a button with a macro to copy the client info into a table on another sheet. This works perfectly.
Now I have another form that will retrieve the customers info in a click of a button in order to make changes to the client. Ei:I now have a second phone number and I want to update that client's info. What I want to do from here is take the info in Cell B2 (clients full Name), Do a vLookup in the "Saved Clients Table" in a Range within my table range. (Range: Saved Clients and the client's full name is in $A:$A and called "Clients Full Name" and once that name is found I need to select the cell in column C and the corresponding row.
This will allow me to copy the info in my form, Paste it in Transpose to the cell that is selected. (I already have that code)
Here are screenshots of what I am trying to accomplish. [URL] .......
View 3 Replies
View Related
