Sum A Column Based On Alpha Characters

Mar 6, 2007

I have columns and rows that are populated with one of three letters: P, S, & T. At the bottom of the column and at the end of the row, i'd like to assign a numeric score to the entire column/row that is based on converting the characters to a numeric eqivelant. P = 3, S = 2 and T = 1. I'm guess a case statement would be used, but not too sure here. So, I'd need the code behind the worksheet as well as the formula(s) used for the score. I've attached an example.

View 3 Replies


ADVERTISEMENT

Sort Column Of Number & Numbers With Alpha Characters Attached

Jan 7, 2010

Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.

Example:

376
377
421
376AB
376XY
377NC
421GQ
421EF

Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:

376
376AB
376XY
377
377NC
421
421EF
421GQ

View 9 Replies View Related

If Statement With Alpha Characters

Apr 24, 2009

Attached spreadsheet - the cell highlighted in yellow.
This is the formula I currently have, but it isn't giving me the results I am looking for:
=IF(TYPESVC="CompanyX", COUNT(Companies!I:I), " ")

Basically, IF the Companies tab, last column has 'CompanyX' written in it - then I want to count the number of responses provided in the Q2N column.

Same formula would be copied over to the other cells to the right as well (for Q5, Q6, Q7, etc.)

View 5 Replies View Related

Countif Based On Alpha And Color Of Cell In Same Row But Absolute Column Reference

Apr 28, 2014

I am currently using a color count function to both count by color and count by color and cells containing certain characters, such as # or %. This works great.

However, I need to modify the UDF to count all the cells in a range in columns C:Z that contain alpha, AND IF the Interior.ColorIndex of a cell in column B within the same row of the counting formula equals the Interior.ColorIndex of a cell in column B within the same row of the selected cell in the range.

Basically, Column B is a header row, and I want to count the cells in a range in each column C:Z if they contain a name AND their corresponding header cell's color in column B matches the color of the header cell in the row containing the formula.

View 1 Replies View Related

Capture Alpha Characters Entered Into A Textbox

Jul 24, 2009

I am trying to capture alpha characters entered into a textbox. If a character is (something) then a variable is (something).

View 2 Replies View Related

Absolute Cell Reference :: Alpha Characters To Increment

Nov 3, 2009

I've got a worksheet here that i need to update every week. The problem i'm having is that i have a table of values that's drawing values in from another larger table. For example in B9 the cell is referenceing the I2 cell in another sheet. When i pull down the formula in B9 i want the cell below to reference J2 and so on. In other words, I only want the alpha characters to increment. Tried messing around with dollar signs but it doesn't seem to be doing what i want.

View 2 Replies View Related

Remove Non-alpha Characters From Alphanumerics With Option To Remove Numbers

Aug 8, 2009

I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).

Option Explicit

Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function

Two requests:

1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?

2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"

View 5 Replies View Related

"Masking" Out Non Alpha Characters From A String

Jul 20, 2009

I am comparing to columns of data from MS Project using Excel. In one column are the resource names for the project tasks. In addition to the names are the designations for % utilized. So the column's data will look like this:

John Jones[15%],Mary Smith, Fred Arguello[240%].

I want to compare this column with another resource names column which will never have utilization values - only the names.

In order to do that I need to "mask" the non alpha characters out of the first string.

View 9 Replies View Related

Selecting Only Alpha Characters From Cell 1 And Merge Another Cell?

Sep 28, 2013

I have an index of 80,000 names from an index. Some names appear in multiple volumes and on multiple pages within a volume. While the name is the same, they are different people. The Roman numeral is the volume and the numeric is the page number.

Example of original data: Joe Shmoe V-225, 310 VIII-22, 86, 110

I have separated the data into separate columns. Now I have:

Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
Joe Shmoe V-225 310 VIII-22 86 110

At this point, I want to combine the Roman numeral in Col 2 with Col 3 and 4 and combine the Roman numeral in Col 4 with Col 5 and 6.

There are way too many records for me to manually enter the Roman numeral where missing.

View 7 Replies View Related

Summation Based On Alpha Season Code

Jul 7, 2009

I am working on an inventory report that the owner would like broken out between in-season items and out of season items. Items with no season code are always in season. Items that have seasonality have a 2 digit season code. A as if Jan, B is Feb, etc. A season code might be CE or KB. CE would be in season from March until May. KB is in season from Nov through Feb and out of season the rest of the year.

We are currently in month G. Is there an easy way to use the two digit season code and determine if G falls in between those two months.

View 6 Replies View Related

Column Number Referenced By Alpha Identifiers

Aug 27, 2013

How can you tell what a column number as referenced by the column alpha identifiers.

Ex: Column AN would equal what column no?

View 2 Replies View Related

Finds Duplicates In Column And Adds An Alpha Character

May 10, 2009

I have a Macro that finds in Column F duplicates and adds an Alpha Character (A-Z) to the last right position in the filed.(1-12 Alpha/Numeric) .

It's set to check all rows until a specific number of rows are reached. The current code does this until intCount and intRow equals 1500. The number of rows vary in each file.

View 6 Replies View Related

How To Detect When # Of Characters In One Column Does Not Match # Defined In Other Column

Feb 19, 2009

how to create a macro that:

- In each row, pops up a Warning Dialog Box if the amount of letters in column "J" doesn't match the # defined in column "B"?
- Preferably this would pop up the Dialog Box as soon as the error occurs (so it's always running)

Two Exceptions:
- Does not look at Rows 1 & 2
- Does not look at Rows where column B = 0 or null

View 9 Replies View Related

VLookup If Match First 2 Characters Of One Column With Another Column

Feb 24, 2014

I have a table as below (you can also refer to the attachment) and need to do the vlookup with EE Code if the left 2 characters of "EE Code" = 91 to 95.

EE Codedeptif left 2 characters of EE code= 91 to 95, vlookup dept code
921600012345678
vlookup arraryA12345623456700
39914123 test785637039914123
38214125 test915580057889056
40964197 test932660038214125
77014155 test913580039914123
345699940964197
945800077014155
953790300123432
915800012345678
045699957889056
585637039914123

View 3 Replies View Related

VBA Max Characters In Column

Oct 20, 2009

I need to know the greatest number of characters contained in any cell in each of the columns in a spreadsheet and display this in row one at the top of each column.

View 9 Replies View Related

Delete Row Based On First 4 Characters Of Cell

May 4, 2006

I need a way to check to see if the first four characters of cell A1 is = 2006. If it is, do not delete the row, else, delete the row. Have tried everything I can think of.

View 5 Replies View Related

Name File Based On First 2 Characters In A Cell

Aug 11, 2006

I'm trying to find a way to save a file based on the contents of cell A2. I've modified the code to Create Worksheets for Each Item in an Excel Table of Data, so that it creates a new workbook for each item. I am also trying to get it to save the master workbook based on the first 2 characters in the cell. Here is the code I have, at the moment (the FAIL.xls is what I put in for testing)

If Range("A2").Value = "YE*" Then
ActiveWorkbook.SaveAs "YE_Master.xls"
ElseIf Range("A2").Value = "NE*" Then
ActiveWorkbook.SaveAs "NE_Master.xls"
Else: ActiveWorkbook.SaveAs "FAIL.xls"
End If

I've tried this, string arrays and everything else I can think of, and it's failing every time.

View 3 Replies View Related

Check A Column For Certain Characters

Feb 6, 2010

I have a column which cells are populated with codes like

BGW
3UG
WWU
1BR
UUBRG
WUBRG
7UUGGRRR

and so on. As you could see, we'll always have a number from 1 to 20 (or no number at all) followed by those letters (which can be only W,U,B,R or G). I'd like to know if there is a function to count the # of times a particular character occurs in a colum. In the above example, the "U" character appears 8 times. This would be nice to automate part of the filling process.

Just a little difference: since numbers always come before the letters, I need to treat a number sequence as a single value: 12GWW should not be seen as 1 then 2, but "twelve", like it was a single character. So, if I have this column...

URR
12WBBB
6UBG
12R
12WG

...then "12" appears 3 times. Nevertheless, B appears four times (3 on line 2 and 1 on line 3.)

View 12 Replies View Related

Remove Certain Characters From Column

Oct 25, 2007

I am trying to reformat the following data: 10-10-14-1W5 needs to look like this 100101001401W500. The full description is actually 100/10-10-014-01W500 the desired result is without the slashes and dashes. I have tried to add a custom cell format of 00-00-000-00L000. but it will not apply to the existing data.

View 5 Replies View Related

Extract Numeric Value Based Upon Ending Characters

Jan 10, 2014

I have a list of numbers in which I need to return the last 5 digits; the length of the string may vary.

However, if the last two digits of the number ends in a specific value then it should skip over the last two numbers and return the preceding 5 digits.

I have tried and have used the very basic Left, Right and Mid functions. My problem is I dont know how to code the formula to identify the last two digits and skip over them, if necessary.

I have provided an attached example.

Extract numeric value based upon ending characters.xlsm

View 5 Replies View Related

Reversing Strings Based On Specific Characters

Jan 1, 1970

This doesn't work

View 13 Replies View Related

Return X Characters Based On X Cells Having Whole Numbers Above Zero

Sep 14, 2007

IF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.

Column - A - B - C - D - E
3 4 / /
5 6 9 / / /

I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.

View 8 Replies View Related

Copy Column But Remove Last Few Characters

Mar 4, 2009

I have a column of data, (10,000 entries), a list of file names basically, e.g:

Mortality Project Executive Summary.pdf
RPP - AA rate and swaps.xls
Commodities.PPT presentation.ppt
Meeting Preparation 20090302.docx

Anywho the point is I want to remove the file extensions (and of course the "."s just before), but some obviously have 4 character extensions, some 3, some 2 etc... Some documents also have "."s in the file name that I do not want to remove, basically just working from the right keep removing until the first "." is removed.
So the final list wants to be:

Mortality Project Executive Summary
RPP - AA rate and swaps
Commodities.PPT presentation
Meeting Preparation 20090302

I know I should VBA it, but I want it all in the one spreadsheet and that's a bit above me. The document has about 30 other columns.

View 3 Replies View Related

Function For Matching Last 4 Characters To Second Column

Feb 21, 2009

I've been trying to find this on the web but haven't a clue now, I have a database query that I am pulling from an access database and now I am trying to match the last four characters of a referral string to a current location, let me show you what I mean. Example,

A(location string) B(current location) C(last location)
car/kitchen/house kitchen NO
kitchen/garage/car car YES
kitchen/car/garage car NO

I want to write a function in column C that matches the last four characters of the location string (column A) with the current location (column B) , and if they match have a YES in column C, or a conversely a NO.

View 6 Replies View Related

Using Macro To Replace Certain Characters In Column

Sep 9, 2013

I have a long list of items in one column, with parameters that are all split by an underscore.
However, in some instances, some of the text that is separated by an underscore needs to be joined together.

So for instance I'll have;

This_is_a_very_good_thing
This_is_a_perfect_thing

Where I want the result to be that "good_thing" becomes "good-thing" and "perfect_thing" becomes "perfect-thing".

How can I get this done with a Macro, instead of constantly doing a Vlookup to change this instances.

View 3 Replies View Related

Custom Sort Data Based On List With More Than 255 Characters

Apr 21, 2014

How to sort a column of data based on a custom list with more than 255 characters.

I have created a named range with 40 entries and then added data validation in the cells of column D using the above named range. However, I want to be able to sort column D in the same order as the named range but the custom data sort lists are restricted to 255 characters.

View 1 Replies View Related

Remove Portions Of Formula Based On Surrounding Characters?

May 13, 2014

I have about 25 workbooks with 180 formulas in them that I would like changed. The formulas are all in the range I10:Q29 and all have the same basic structure:

[Code]....

I would like to remove "filepath1!cell1*filepath1!cell2-" and "filepath1!cell2-" so that only "(filepath2!cell3*filepath2!cell4)/(filepath2!cell4)" remains.

My VBA is pretty weak, but I imagine it should be possible to use something like the VBA equivalent of len() and mid() to isolate the components of the equation to the left of the - in the numerator and denominator, and remove those components (as well as the -) from the formula.

Attached File : trim formulas.xlsx

View 12 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

Launching Macro Based On Selected Cell Value (first Two Characters?)

Sep 6, 2013

I have a macro to sort a very large list of materials by a variety of conditions.

The macro runs automatically when a cell on the master list spread sheet is selected and using If/ElseIf it sorts based on what cell they have selected.

Example
If ActiveCell = "ALUMINIZED" Then
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=2
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=4
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=1, Criteria1:= _"Aluminized"

Which is working perfectly. The problem I am having is that some of the cells are two lines so it looks like this:

BE
BEARINGS

I don't know how to get it to launch based on that cell content because of the line break in there. Can I make it launch based on the first two characters only?

View 5 Replies View Related

Restrict Cells Text To X Characters Based On Corresponding Cell

Apr 14, 2009

I'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.

I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved