Sum Based On String

May 21, 2007

How 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, 2013

Working 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.

View 6 Replies View Related

Generating Random Number String Based On Alphanumeric String?

Aug 14, 2014

Wondering 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

View 7 Replies View Related

IF Statements Based On Text In A String

Dec 12, 2008

I 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

View 4 Replies View Related

Editing String Based On Length

Sep 1, 2009

I get all my results from a CSV file. This isnt 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?

View 9 Replies View Related

Formula Based On Value Within Text String

Jul 23, 2014

I 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.

View 9 Replies View Related

If String And Formulas (calculation Based On The Selections)

Aug 27, 2009

in 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.

View 5 Replies View Related

Sorting Range Based On String Variable

Jul 31, 2014

I 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] ..........

View 3 Replies View Related

Insert Row Macro Based On Text String

Nov 20, 2009

I'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.

View 6 Replies View Related

Select 4th Cell In Column Based On String

Dec 12, 2011

Data 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

View 3 Replies View Related

Condition Statement Based On 2nd To Last Character Of String?

Apr 10, 2012

I 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.

View 2 Replies View Related

Sorting Sheets Based On A Text String

Mar 16, 2008

I 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.

View 9 Replies View Related

Insert New Row With String Based On Conditional Statement

Sep 6, 2008

I'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 Related

Delete Row Based On Partial String Criteria

Dec 10, 2009

I 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:

View 9 Replies View Related

Action Based On Individual Values In String

Aug 10, 2006

I 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?

View 3 Replies View Related

Take Action Based On String Value In Selected Cells

Mar 22, 2007

I 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

View 2 Replies View Related

Find Text In String Based On Pattern

May 5, 2007

I 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 Related

Find Most Common Occurring String Based On Lookup Value?

Dec 4, 2013

Find 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?

View 4 Replies View Related

Summing Multiple Items Based On A String In A Cell?

Jan 24, 2014

I 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

View 7 Replies View Related

Auto Generation Of String Based On User Input

Jun 3, 2009

Without 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.

View 3 Replies View Related

Highlight Matching String In Listbox Based On Textbox Value

Jun 26, 2014

I 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.

View 4 Replies View Related

Sort Pivot Table Based On Partial String...

Oct 6, 2008

I'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.

View 3 Replies View Related

Change Cell Colour VBA Based On Text String?

Oct 11, 2011

I 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?

View 2 Replies View Related

Excel 2010 :: Set Row Height Based On First 14 Characters In String?

Feb 23, 2012

Excel 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.

View 1 Replies View Related

Returning String From One Cell Based Upon Multiple Cells

May 22, 2013

I 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.

View 4 Replies View Related

Segregate Data Based On The The First String Of Each Security Description

Jun 12, 2009

a 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 ...

View 9 Replies View Related

Formula: Split A Text String Based On A Symbol

Oct 13, 2009

is 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 Related

Summarize Data Based On Partial String Matches

Jun 30, 2006

I 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'.

View 9 Replies View Related

Return A True/false Based On A Cell Containing A String Vs A Value

Jul 20, 2006

how 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).

View 9 Replies View Related

Vba Code To Extract And Clean Data Based On A Set String Ending

Dec 5, 2013

I 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.

View 4 Replies View Related

Split Into Multiple Sheets Based On Beginning String In Set Column?

Apr 12, 2014

I have used this code below to split a large excel file into multiple sheets from matching column data, but now I need to split it by a partial match (set number of characters from the beginning) from beginning of the column data.

For Example:

[Code]....

So with the code provided below using column 3 I would get 10 different sheets since none of the data in the column is identical. I want to modify the code (or come up with new code) so I can set the number of characters to compare from the beginning of the data in the set column and split into sheets based on that. So if I set it to the first 4 characters in column 3 I would receive only 5 sheets sheets: Safe, Fail, Dont, Poop, & 21-4.

What are the modifications or new code needed for this? I have searched for a bit with no luck, just keep finding code to check the full cell data for matches in a set column like this code I have:

SPLIT DATA FROM ONE SHEET TO MULTIPLE SHEETS

[Code] ......

View 1 Replies View Related

Search Text String And Return Value Based On Multiple Criteria?

Apr 8, 2013

Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?

E3a7qta.png

View 1 Replies View Related

Assign Specific Number To Cells Based On Another Cell Value In String

Feb 27, 2013

So I have this list (I made it a little bit shorter).

So what you see is two different tasks (01 and 02) and three different conditions (A, B and C). In column B you see the result I would like to have. '/Searchtask_01.html' in A1 belongs to conditions A, because it is in session A. However, '/Searchtask_01.html' in A10 belongs to conditions B, because it is in session B.

How to get the results in B with a formula?

View 1 Replies View Related

Insert Column Macro Based Variable String Letter

Jun 11, 2008

I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).

View 4 Replies View Related

Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4

test.bmp‎

View 2 Replies View Related

Search For Text In String - Filtering Of People Based On Country Code

Jul 5, 2014

A
B

1
Terry lives in England
=FORMULA

2
Claire lives in Spain

3
Paul live in France

I'm looking for a simple formula within the cells of column B to populate the cell with data based on the "country" mentioned in the string of cells in column A. There will be 20 countries and dependant on the country mentioned a 3 character country code will be entered in column B allowing for easy filtering of people based on country code. so something along the lines of if A1 contains "England" B1 equals ENG etc (for abot 20 countries)

The output should look like below

A
B

1
Terry lives in England
ENG

2
Claire lives in Spain
ESP

3
Paul live in France
FRA

View 8 Replies View Related

Conditional Rank Based On Whether A Criteria Cell Includes A Specific Text String

May 8, 2009

I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.

So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.

How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).

As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.

The answer I need is 2 because, discounting the zero value, 126 is the second highest value.

View 9 Replies View Related

Excel - UDF That Returns String Of Multiple String Objects / Possible To Color Font?

Sep 19, 2012

I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.

Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):

VB:

Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String
StringReturn = Str1 & Str2 & Str3
End Function

Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3

View 2 Replies View Related

Change Current Cell Value If Number String NOT Letter String?

Apr 7, 2014

In sheet1 I have a simple database consisting of 5 columns of data

Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500

what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.

View 4 Replies View Related

Conditional String: Repeat The Same Account Number In The String

Jan 10, 2009

I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?

View 4 Replies View Related

Replacing Misspelled Sub-string In Varying Full String

Aug 27, 2009

I have a situation where a word (in this case "Restaurant") is misspelled in a list of about 78,000 location names. The location names are in one column, and the [misspelled] word "Restaurant" is anywhere between the first word of the string, to the last, with any amount of alphanumeric/symbol characters between. For example:

Alice's Restaurant
Alli's Restaurant & Bar
Alexis Restaurant of Waukesha
Amigo's Mexican Restaurant #2

I want to replace any misspellings of the word with correct, but since the list is so long, and the way the word is misspelled varies so much, going through manually is entirely too time-consuming.

Some of the variations I've seen so far are Resta, Restau, Restaur, Restuara, etc...

Is there a way to search and replace cells that contain the text in any location of the string? Specifically, where ever there is JUST "Restau", replace with "Restaurant", regardless of where it is in the string? This way, trailing text is not deleted or manipulated, i.e. "Alice's Restaur and Bar" will change to "Alice's Restaurant and Bar".

View 14 Replies View Related

ADVERTISEMENT