Sum Based On String
May 21, 2007How to summing total base on certain string?
A sample is enclosed
ADVERTISEMENT
Select Based On Partial String Then Delete Based On Full String?
Jun 13, 2013Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.
Here is the format of the report at this point. Names have been changed to protect the innocent.
Agent: 2366 Bacon, Kevin
Date
Start
End
[Code].....
So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.
I know what I want to do...but not shure on the code cause I am not great at VBA.
Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.
Generating Random Number String Based On Alphanumeric String?
Aug 14, 2014Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234
Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
IF Statements Based On Text In A String
Dec 12, 2008I want to return Y or N for each row where a certain word (lets say "Apple") appears anywhere in the reference cell. Therefore in the below example the first 3 rows contain apple (somewhere in that cell) so Y is returned. I can get it to work when apple isd the only thing in the cell but not if it appears with text before and/or after it.
ie:
A B
apple Y
apple orange Y
orange apple Y
pear N
grape N
Editing String Based On Length
Sep 1, 2009I get all my results from a CSV file. This isn’t a problem except for the first nine days of the month where excel cuts off the first 0 so 01012009 reads 1012009 and cannot be properly concatenated.
DateNameItem
1012009BobKeyboard
1012009SidMouse
1012009DaveMonitor
1012009EdMonitor
1012009AndrewKeyboard
1012009DanKeyboard
1012009BruceKeyboard
What I would like to do is have VB read the contents of the date cell and if it is less than eight characters, edit the cell by adding a zero at the beginning of the string. This needs to be a one off process for all cells. Can anyone come up with code that might make this happen?
Formula Based On Value Within Text String
Jul 23, 2014I have been trying to work out a formula without success so far. If you look at the this text string
Code:
Depart Doncaster Depot with a Mini bus at 0649
The "Mini" word in red is the one that I am trying to work out with.
Here is the problem. The "Mini" word could be replaced with 7 other words - Walk, Artic, Dart Smart, Maxi, Smart, Staff Car, or just bus with no explanation as to what type it is.
I need a formula to look at the text string and work out which one of the above categories are included in the text and based on that gives me either W, A, Dart Smart, Mx, S, Car, and the last to be just N.
If String And Formulas (calculation Based On The Selections)
Aug 27, 2009in cell A1 the user will select from a drop down list 5.50, 11,22, 33, 55. in cell B1 user selects from the drop down list a number 1 - 9. now in cell c1 I wont it to do some calculation based on the selections.
If b1=3, then A1*.20, if B1=2, then A1*.30, If B1=1, then A1*.50 and if B1 is greater then 3, post 0, if B1 is empty, then leave c1 empty.
Sorting Range Based On String Variable
Jul 31, 2014I have a spreadsheet with column headers like, "Date" "Phone number" "Name" ect., and rows of data below. Each of these rows contains data pertaining to a prospect employee, as the purpose of the spreadsheet is to keep track of job applicants. One column is used as a 'Recruiter Sign-off' area, where the recruiter who processed the application inputs their name:
Example.png
I'm trying to automate a variation of excels sort function. However, I don't want to sort 'From A to Z.' Instead, I want to have the rows of data sorted with the 'Recruiter Sign-off' column used as the sort Key, and a selected name (of one of the recruiters) used as the sort Criteria.
I already have a userform that allows the user to select a name from a listbox. When they press confirm, the name is stored in a Public String variable, (selectedRecruiter). I'd like then like to have the rows below the column headers to be sorted based on the selected name. Example: User selects the name "Jon" from a list. The application then brings all of the applicants that 'Jon' has signed off on (those rows that contain his name) to the top rows of the range.
The workbook is shared, so I cannot use tables.
I found a code that accomplished what I want, however it also leads to a horrible system crash after a few times running it (BEX crash).
[Code] ..........
Insert Row Macro Based On Text String
Nov 20, 2009I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler
The Data Begins in Row 5 of Column A
My Current Macro is as below which I have adapted from another one I used
It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.
Select 4th Cell In Column Based On String
Dec 12, 2011Data example:
AB1AREANumber2SW103SW114SE125NE136SW147SW158SW169NW17
I need a code in VBA that can find the 1st cell in col A that contains "SW" and store the number in col B as V1= # then find the 4th cell in col A that contains "SW" and store the number in col B as V2= #.
so that i can do some math with the numbers. The code should result in 10-15
Condition Statement Based On 2nd To Last Character Of String?
Apr 10, 2012I am trying to pull certain members loads from our global design force spreadsheet and because of the naming convention we used for our structural members the 2nd to last character is unique to what I am looking for. I am trying to get a simple condition statement that will display the load only if the 2nd to last character of the name of the element is satisfied.
For Example, if "T" is what I am looking for then :
EDT4 will be true and give me the value accordingly
D10T1 Will also be true and return me the value
D10B2 Will not be true
I know how to use the Mid() and right() function, but I need to check the 2nd from the right.
Sorting Sheets Based On A Text String
Mar 16, 2008I have a sheet named: tool
In this sheet I have a long list of data that i would like to have sorted out on different worksheets that needs to be created by the macro. The deciding factor for the sorting are found colum B. In this colum there are different text strings. I would like for the macro to look at this text string and copy the entire line over to the respective work sheet. the work sheet needs to be named according to the text string in colom b.
Furthermore in the sheet named tool the first 3 lines are headings and i would like all of these transfered to each of the new sheets.
Insert New Row With String Based On Conditional Statement
Sep 6, 2008I've been working on this project to propagate certain products through multiple categories. I chose to use excel to assign multiple categories to each product. On sheet1 I have setup products (column a) and qualifiers in the following columns (color, model, etc). What I would like to achieve is for the user to select yes or no for each category column and and if yes then have the corresponding category breadcrumb string (from sheet2) inserted into one specific column in sheet3. As the user continues to select multiple "yes" from the category columns for that single product, those additional category breadcrumbs get inserted at the end of the list in sheet3.
View 9 Replies View RelatedDelete Row Based On Partial String Criteria
Dec 10, 2009I have 2 columns A and B. The data is in column B.
What I'm trying to do is delete entire rows from my current selection if they do not begin with mailto:
Action Based On Individual Values In String
Aug 10, 2006I need to read a binary value and trigger an action based on whether the each value is 1 or 0.
So for binary 1001
1 switch on
0 switch off
0 switch off
1 switch on
Assuming i know how to switch on and off does anyone know how to make the determination based on individual characters in a binary number string?
Take Action Based On String Value In Selected Cells
Mar 22, 2007I am trying to take some action based on the string values in selected cells. Here is my code to iterate through selected cells and count the occurances of certain string values:
Private Sub TakeAction()
Dim cel As Range
Dim sel As Range
Dim complete As Integer 'a counter for completed
Dim pending As Integer ' a counter for pending
Dim cancelled As Integer ' a counter for cancelled
Set sel = selection
For Each cel In sel
If cel.Text = "Some text" Then
complete = Complete +1
If cel. Text = "Some other text" Then
pending = pending +1
If cel.Test = "Yet some other text"
cancelled = cancelled +1
End If
End If
End If
Next cel
MsgBox complete
MsgBox pending
MsgBox cancelled
End Sub
Find Text In String Based On Pattern
May 5, 2007I am wanting to find within a cell the following pattern "ANNNN" - "A" meaning any alpha character A-Z and "N" meaning any numeric 0-9. The pattern ANNNN could be anywhere within the text. If the cell contains text with that pattern the text would be deposited in the adjacent cell. Lorem ipsum dolor sit A1234 amet, consectetuer adipiscing elit. Mauris tristique. Cum sociis
View 5 Replies View RelatedFind Most Common Occurring String Based On Lookup Value?
Dec 4, 2013Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:
Capture.PNG
- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on.
- I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.
I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?
Summing Multiple Items Based On A String In A Cell?
Jan 24, 2014I have columns (1-7) containing values of time these columns are labelled G,R,A,S,D,B,T by 850 rows (which are locations/jobs)
The next set of columns (1-7 determins what week the work takes place) so you will get a G in a cell or GR etc.
I would like a formula to work out the sum of the time columns by the code in the corresponding cell - I am stuck!
Seantc example.xlsx
Auto Generation Of String Based On User Input
Jun 3, 2009Without using VBA, how can i generate strings say
x1
x2
x3
x4
x5
assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.
Highlight Matching String In Listbox Based On Textbox Value
Jun 26, 2014I thought I would get this on my own, but I digress.
I received sample code to populate a listbox from tab names of a workbook, yay!
What I have attempted is to use a user form, enter a string in a textbox and if it match's any string in the list box, then highlight.
Basically, its a simple search engine.
Sort Pivot Table Based On Partial String...
Oct 6, 2008I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,
Standard Kitchen
Standard Master Bath
Standard Hall Bath
Optional Kitchen
Optional Basement Bath
First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.
Change Cell Colour VBA Based On Text String?
Oct 11, 2011I need to change the colour of a cell using VBA based on the input of a certain word. I have used teh below code but it doesn't pick up different variations of the word
Dim cell As Range
For Each cell In Range("E2:E500")
If cell.Value = "Check" Then[code]....
This code works fine (i have put it into worksheet change) when I use "Check and Mark and Chase" but some users are bound not to use capitalised first letters and on these occasions the cell colours are not changing.
How to add something to thie code above to make it work for any variation of capitalisation?
Excel 2010 :: Set Row Height Based On First 14 Characters In String?
Feb 23, 2012Excel 2010; I am setting row heights on the basis of some parameters. I have the following statement;
If targetCell.Value = "Photo Comment: " Then
targetCell.RowHeight = 185
But what I really want is to recognize only the phrase;
"Photo Comment:" in a text string that might be much longer e.g,
"Photo Comment: The photo above depicts yata, yata.........."
So, I want to recognize the first 14 character as "Photo Comment:" and then have the row height adjust to 185. So it involves "Left" and "Len" (I'm thinkin') but the syntax is beyond me.
Returning String From One Cell Based Upon Multiple Cells
May 22, 2013I need to return the specific text of a cell based upon the text in multiple cells.
Worksheet as follows:
Asphalt Sub:
BLDG1-Backwash 1
*BLDG1*
*Sub*
BLDG1-Backwash 1 OverEx
*BLDG2*
*Sub*
[Code].....
I have three columns thus far. I need to add another column and search all items in Column A using the criteria in Columns B and C. For instance I need to search for BLDG1-Backwash Sub: and place it into column D using the criteria in Columns B and C.
If possible, I would also like to remove both the words, BLDG1 and Sub from the result so then it only shows "Backwash".
My spreadsheet is much larger than this and I have muliple buildings I am dealing with, sometimes 30-40.
Segregate Data Based On The The First String Of Each Security Description
Jun 12, 2009a macro that will segreate data based on the the first string of each security description under the heading Security description and then sum the total on a separate row in the total column.
Starting row hearder in sheet "Raw_Data" start in row 6 and i would require the same in sheet "Result
Sheet "Raw_Data ...
Formula: Split A Text String Based On A Symbol
Oct 13, 2009is there a function that will split a text string based on a symbol. I know how tyo use left and right, which are based on a set number of characters but I want to split based on a "/" mark. whats to the left of the "/" mark and whats to the right of the "/" mark. any ideas. an exaplme is: tom / tim. i want a formula that will put the word "tom" in a cell and another formula that will put "tim" in another cell.
View 9 Replies View RelatedSummarize Data Based On Partial String Matches
Jun 30, 2006I have two questions:
a. Check the code below:
Dim news1 As String, news2 As String
news1 = "new"
news2 = "polygon"
Dim countie As Integer
For buddie = 2 To b1.UsedRange.Rows.Count
'If b1. Cells(buddie, 1).Value Like "new*polygon" Then
If b1.Cells(buddie, 1).Value Like news1*news2 Then
countie = countie + 1
n1.Cells(buddie, 10).Value = "test"
End If
Next buddie
I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.
How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.
b. If i have a value in the cell:
/new/blahblah/anycra/polygon
how do i split it so as to assign two variables to have 'new' and 'polygon'.
Return A True/false Based On A Cell Containing A String Vs A Value
Jul 20, 2006how return a true/false based on a cell containing a string (text) vs a value?
I have a column where most cells are blank, some contain text, and others contain dates. I would like to select and manipulate only the ones that contain dates (or values).
Vba Code To Extract And Clean Data Based On A Set String Ending
Dec 5, 2013I would like to extract competition naps from the competition entries and then remove the string ending "nap" from the data so it can be pasted to another sheet in it's 'clean' format.
on the example sheet column B will always be the first column and the range will extend to either G,H or I (always the max range as there are always 6 to 8 races)
i would like the code to search through these columns of data and find the cell ending in 'nap' then to return this in column i, once done remove 'nap' ending from both the source cell and column i.
once done to then go through all the rows and do similar
the end result would be all naps returned into cell j and all the 'nap' endings through the data range B to I (max) to be removed to leave the horses name only.
in book 3 the first row nap was sommersturm so i have shown the outcome i would like with the nap ending in I1 removed and the horse name returned in J1 again minus 'nap' ending.
the data will always be clean with no leading/trailing/excessive spaces and always be lower case too. sometimes a space is not in between horse name and nap but it's always last 3 characters i want removed still.