Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Sum Based On String


How to summing total base on certain string?
A sample is enclosed


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Editing String Based On Length
I 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?

View Replies!   View Related
IF Statements Based On Text In A String
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 Replies!   View Related
Insert New Row With String Based On Conditional Statement
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 Replies!   View Related
Delete Row Based On Partial String Criteria
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 Replies!   View Related
Insert Row Macro Based On Text String
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 Replies!   View Related
Sorting Sheets Based On A Text String
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 Replies!   View Related
If String And Formulas (calculation Based On The Selections)
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 Replies!   View Related
Action Based On Individual Values In String
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 Replies!   View Related
Find Text In String Based On Pattern
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 Replies!   View Related
Take Action Based On String Value In Selected Cells
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 Replies!   View Related
Formula: Split A Text String Based On A Symbol
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 Replies!   View Related
Return A True/false Based On A Cell Containing A String Vs A Value
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 Replies!   View Related
Segregate Data Based On The The First String Of Each Security Description
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 Replies!   View Related
Auto Generation Of String Based On User Input
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 Replies!   View Related
Sort Pivot Table Based On Partial String...
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 Replies!   View Related
Summarize Data Based On Partial String Matches
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 Replies!   View Related
Insert Column Macro Based Variable String Letter
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 Replies!   View Related
Conditional Rank Based On Whether A Criteria Cell Includes A Specific Text String
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 Replies!   View Related
Replacing Misspelled Sub-string In Varying Full String
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 Replies!   View Related
Conditional String: Repeat The Same Account Number In The String
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 Replies!   View Related
Extract Alpha From A String And Compare With Another String
I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!

View Replies!   View Related
If Text Found In String Return String
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.

View Replies!   View Related
Finding The Frequency Of A String In Other String(s)
I'm looking for a formula that will count specific word in a cell.

Let say cell a1 has "WLLWWLLLLLLWWLLLWWWLLLWWLLWWLWLWLWLLLWWW"
I want to know how many WWLL are in this cell, which is 4.

I wrote this custom function to provide the answer. It is used like so: ..

View Replies!   View Related
Replace String Within A String
How to extract out the (M) from a string and convert (M) in that string to another value? Example: I want to extract out the (M) from a string and convert the (M) to "Medium". The data is: Burger (M) with french fries. How to exract (M) from the data and convert the value (M) to "Medium"?

View Replies!   View Related
Convert A String To A "real" String (vba)
How can any string valid for the name of a workbook be converted to a string correctly recognized by Application.Run? Sometimes a string is not really recognized as a string. See for example: .....

View Replies!   View Related
If A String Contains @
I am trying to write in VB some code that will enter a number in a column if a cell has some data in it and one character is an @. see attached spreasheet.

View Replies!   View Related
String Within A String
say I have two files named "Model Template" and "A_Model" and I am trying to allocate the file names to string variables. The file names represent 'families' of files with varying names that can be distinguished by the presence of the strings "Template" and "_". Why would the Instr method as used below not work?

Sub WorkbookNamesStorei()
Dim Workbooki As Workbook
Dim NameWorkbooki As String
Dim SensisBook As Workbook
Dim NewModelBook As Workbook
Dim a As String, b As String
a = "_"
b = "Template"
For Each Workbooki In Workbooks
NameWorkbooki = Workbooki.Name
If InStr(1, a, NameWorkbooki) > 0 Then....................

View Replies!   View Related
To Have Each Data String In Its Own Row
S 03 01/22/08 03:09:55 3 ---- ----

A 03 01/22/08 03:10:01 4 .175 .175

P 03 01/22/08 03:10:15 3 ---- ----

A 03 01/22/08 03:19:09 3 .107 .107

A 03 01/22/08 03:19:34 4 .360 .360

A 03 01/22/08 03:19:53 1 .157 .157

the first single letter is the test evaluation code - "A" for accept - "P" for Stopped - "S" Severe Leak - Among others.

the "03" stands for the program number which there are three of - "01" "02" "03" --- These can be changed at anytime but by the user of the tester.

then of course the date

the the time - military style

the next number represents the test station - station 1,2,3, or 4

and the last group stands for the "leak rate"

------------
A couple questions I have...
1.) right now all the data is being placed into Cell A1 only. I need to have each data string in its own row. How can this be done.

2.) It would be great if I would be able to break up the string into separate columns. -- Like "P" in column A. "03" in column B. date in column C. etc...

3.) And maybe if those are simple enough to accomplish would I be able to separate into different sheets? preferable separate them by the "station number" which is the 5th group in the data string.


View Replies!   View Related
VBA String Manipulation
I need to be able to take someones name which is in a single cell in the format "First Middle Last" (could be more than one middle name) and then split it up so that it fits the following format:

Last name (must be 50 characters)
Middle name (must be 15 characters)
First Name (must be 15 characters)

I'm unsure on the right combination of operators to use to variably concatenate strings in this way. I'm confident I can figure out how to add the white spaces after a name to make it fit the character requirement but I don't know how to split the initial name up into it's 3 parts. Names longer than the character requirement will just be truncated.

View Replies!   View Related
Use Mid To Extract String
I need to extract the term between the two colons: Snowboard Jackets

This term resides in AB289-
Snow : Snowboard Jackets : Guys SoftShell

Using MID, but not able to get all the term


View Replies!   View Related
Add A String At The Beginning And End
I have a column with data and I want to add, in each row, the following:

at the beginning of row:{color=blue}
and right at the end of the row: {/color}

so that

excel

becomes

{color=blue}excel{/color}

I tried to do this with the formula:

"{color=blue}"&A1&"{/color}"

which yields nothing.


View Replies!   View Related
Substrings From String
I'm trying to break down a string and put parts into designated cells using the following
code...

View Replies!   View Related
VBA - Search For A String
Scenario:
A1:A2000 (or the wole column) will have the following:

Black Total
Grey Total
Total Grey Print
Blue Total Print
and so on.

Each cell will contain the word "Total". In B1:B2000 ( or the whole column) I would like to remove the word total from the column A. Example:

B1 should only contain Black (instead of Black Total)
B3 should only contain Grey Print
B4 should only contain Blue Print


View Replies!   View Related
How Do I Split A String Twice?
So I have strings like the one below. What I'm looking for the text behind the ":" being, 17-SEP-2009, 000737-EWO, SRS and finally 002-YAO

Example string:

date: 17-SEP-2009, number:000737-EWO, detail: SRS, DD:002-YAO

My ...

View Replies!   View Related
Reverse A String
I have a cell (a1) that contains "Kent; Andrew George" and I want a formula to change this to "Andrew George Kent" (b1)


View Replies!   View Related
Break Apart A String
breaking apart a string in a cell.

in cells A6:A112 my trial balance export from QB Online is account # [SPACE] Account name:Sub Account:Sub Account

eg. 70160 Administrative Expense:Travel & Entertainment:Travel & Ent.-Travel & Lodging

I would like to break the string apart so I have only account # in column "a" and the right most sub account name in column "b". So if I use my example from above, I would have 70160 in "a" & Travel & Ent.-Travel & Lodging in "b". Note all the account name & sub accounts are separated by ":"

View Replies!   View Related
Extract Last Name From A Name String
I have a list of names in a data set like the examle below. I would like to be able to isolate the last name so I can match the data in this data set with a data set with additional information. The only common set of data I have is the first and last names.

View Replies!   View Related
String Comparision ....
I am having my data from A1:A1000 in which I want to know if there are any repeatitions. The problem is that the order of words may not be same. For example strings "I am fine" and "Fine I am" are to be recognised as repeatitions.

Can anyone out there help me to make my task easy by giving me a macro.

View Replies!   View Related
String Splitting
If I have a string which contains 3 words, is there an easy way of separating out the consonants into one string and the vowels into another (spaces should be ignored)?

The only way I can think of doing it is to go through the string item by item and comparing the letters to a list of vowels and using that to do the separation.

View Replies!   View Related
Indicate That A Variable Is String
I understand that a everything is already considered to be string, but how would I phrase this?

If txtBolow = (a statement which indicates the textbox is full) Then

txtBolow. SetFocus
txtBolow. SelStart = 0
txtBolow. selLenght = Len(txtBolow)

If txLowl = ("") Then
txtLowl. SetFocus
txtLowl. SelStart = 0
txtLowl. selLenght = Len(txtBolow)

What would I use to indicate that the user has completed the data in the textboxes, and the old data is highlighted to make way for the new?

View Replies!   View Related
Nth Character From String
I would like to get the nth character from a string. For example, let's say there is a string "jupiter", and I would like to get the 3rd character of this string, "p". Is there a function in excel vba that would allow me to do this?

View Replies!   View Related
IF THEN String For 2003
I'm looking for a formula to say if the contents of cell A1 are this and the contents of cell L1 are this, then enter value 1 in cell N19.
For example if A1=Text and B1=Diff Text then A14=1, but if A1 doesn't =Text or B1 doesn't = Diff Text then A14=0.

View Replies!   View Related
Parsing A String
I am trying to figure out how to parse some strings. Example of strings

View Replies!   View Related
Break The String
how to break the string into 3 variables.

Example: Given string is : -4.98e-005x^2+0.368x+0.0588
All the string there will be two + symbols.

I have to break the above string into 3 parts and store it into variables like
a=-4.98e-005x^2
b=0.368x
c=0.0588

View Replies!   View Related
String Compares
My program assigns a variable one of three values - either "Lancaster", "DeSoto" or "Cedar Hill". My string compares for Lancaster and DeSoto work fine but can't get anything happening for Cedar Hill.

I looked at the value of the variable and it appears to be correct. However, when I did a StrComp for the variable and the string "Cedar Hill", they did, of course, not come out as equal.

View Replies!   View Related
Replace The First Or The Last From String
I need a code which replaces the given letter or sign combination at the first or at the last position of a string. User should be able to chose from which end the replacement should be done, what should be replaced and in which column. Maybe throug a messagebox!

For example (1):
Column B:
[trn][m1][c blue]$ [/c] = Dollar. [/m][m2][c green]Currency of USA[/c][/m][/trn]
[trn][m1][c blue]€ [/c] = Euro. [/m][m2][c green]Currency of EU contries[/c][/m][/trn]

Result of replacing the first "[/c]" with "" should be:
[trn][m1][c blue]$ = Dollar. [/m][m2][c green]Currency of USA[/c][/m][/trn]
[trn][m1][c blue]€ = Euro. [/m][m2][c green]Currency of EU contries[/c][/m][/trn]

Result of replacing the last "[/c]" with "" should be:
[trn][m1][c blue]$ [/c] = Dollar. [/m][m2][c green]Currency of USA[/m][/trn]
[trn][m1][c blue]€ [/c] = Euro. [/m][m2][c green]Currency of EU contries[/m][/trn]
......................................

View Replies!   View Related
Use String To Set As Commandbutton
I have a large number of commandbuttons on a userform that I wish to apply captions to via a for loop. Each button is numbered appropriately for this purpose. e.g.

Button_1, Button_2 etc etc

I want the loop to compile a string that represents the button name e.g.

String = "Button_" & Loop_Count
and then use my defined commandbutton:

Dim My_Button as commandbutton
to name the buttons. So the loop would be something like:


for Loop_Count = 1 to 100

String = "Userform_Name.Button_" & Loop_Count
set My_Button = String
My_Button.caption = "Test_" & Loop_Count

next Loop_Count
Unfortunately trying to set 'My_Button' using a string like this doesn't work.

View Replies!   View Related
Using 'Now()' As A Numeric String
I am trying to create what should be a unique string.

I figured the easiest way to do this would be to use the value given by Now().

However I can't find out how to actually get the result of Now() as a number.

If on a spreadsheet I put in a cell: =Now() and then format that cell as 'General' it gives me the number I am trying to get programatically.

I tried:

View Replies!   View Related
Vba To Get The Numeric Value From Given String
IDMacro Out Put
X11891189
cdecde
CONFRCONFR
175175
D0101101

I ahve a sheet having two column one is "ID" and other is "Macro Out Put" as shown above , is that possible with VBA code that it give the same result as i shown above in column "Macro Out Put". for reference i also attch the file

View Replies!   View Related
Parse A String?
If I have a cell that has the following information: DEF(352) HHY(24533) KLDD(3334)
And I extract that to a String with the following

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved