Case Sensitive Counts
Jul 1, 2006
I have a spreadsheet which I use to count the occurrences of a value against a range. Basically if the value is uppercase I count as 1 and for lowercase 0.5
This spreadsheet is used as a holiday chart and I have previously used the formula below however, for some reason it no longer works. Not even if I change the lookup value.
The exact function takes a single cell as its value but I have a spreadsheet which allows a range. Although I cannot change it….
=SUM(IF(EXACT(B1:AF99, "LB"),1))+SUM(IF(EXACT(B1:AF99, "lb"),0.5))
View 3 Replies
ADVERTISEMENT
Jun 8, 2009
I have been battling with excel for weeks now trying to get some data to display the way I want it to.
The plan is to enter (into individual cells) some letters, some lower case, some upper case ie, A A a A a C d. Some of these will be displayed together in another cell like this: AAaA, AaCd. My problem is that I want them all to be displayed Upper case letters first, lower case second like this: AAAa, ACad. No amount of my inexperienced fumbling with excel's tools has worked so far.
I am playing around with a table for genetics so if my results in a cell are aAbBCcdD, I need it to display: AaBbCcDd. Is it possible?
View 14 Replies
View Related
Dec 17, 2007
in making this macro non case-sensitive. So when a user searches a company name in the worksheet they do not require to type in the company name exactly as it appears.
For example; when searching Microsoft they can type "microsoft" and the macro would take the user to Microsoft.
I would also like to know if it is possible to add a feature that keeps the search dialogue open so the user can search the next possible match. If the user was to type in "mirco" and the search would show the user any company name with the word micro in it.
Sub Button3_Click()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
View 9 Replies
View Related
Nov 2, 2006
is it possible to make a SUBSTITUTE finction non- case sensitive?
For example I want to replace all letters "e" and "E" in a cell.
View 3 Replies
View Related
Aug 12, 2007
I have recently taken on the task of creating a large protected program (without macros) that automatically updates a PowerPoint presentation. I created corresponding workbooks for each sheet in the original (master) workbook. The corresponding workbooks are then used to paste object into the power point presentation. The only problem is it will not update format (text color) changes into the corresponding workbook. I have solved half the problem with conditional formatting, but I want to conditionally format the color of text to two different colors blue if Aa and Red if all CAPS. how to differentiate between Standard word and All CAPS in the conditional formatting.
View 7 Replies
View Related
Feb 25, 2008
I need a macro that will format a a column based on content that is case sensitive. Example, a cell that contains a capital 'P' will be shaded gray with red text where a cell that contains a lower case 'p' will not change format.
View 3 Replies
View Related
Sep 12, 2009
Is it possible to have this type of case sensitive sorting (first all words beginning with a capital later and then all cells beginning with a lowercase letter):
A
B
C
D
E
F
G
a
b
c
d
e
f
g
View 4 Replies
View Related
Jan 17, 2014
I've 2 lists that contain similar info but 1 of the lists holds info I need to join.
I've a unique case sensitive id that I'd like to use to join the lists.
I usually use VLOOKUP but it is case insensitive and I cannot understand how to make it case sensitive.
The 2 lists are in 2 separate worksheets as they each hold a lot of data.
View 3 Replies
View Related
Aug 15, 2007
I have a user form to get some input from the user and want to make sure that in some textboxes user should be able enter only text i.e A to Z or a-z no numbers or special charecters.
Private Sub CommandButton1_Click()
Dim RegEx As Object
Dim Strng As String
Strng = CStr(Me.TextBox1.Value)
Set RegEx = CreateObject("vbscript.regexp")
With RegEx
.Pattern = "^[A-Z]{2}/d+/d{2}$"
If Not .test(Strng) Then MsgBox "Invalid Format: TextBox1"
End With
Set RegEx = Nothing
End Sub ..............................
View 9 Replies
View Related
May 22, 2013
Suppose I have some data in a column like below
Blue
Red
Green
green
red
RED
BLUE
I need a macro or excel formula so that it will remove duplicate products- case sensitive(removing all duplicate prod using using excel,that i know.) i.e. it should not consider Blue BLUE as duplicate. only consider Blue Blue as duplicate.
So the macro should work same as data -> remove duplicate function already exists in excel, except it will be case sensitive..
View 7 Replies
View Related
Mar 2, 2012
I am using the this formula to look up data using 0 to show not found. VLOOKUP is acting case sensitive. The Master Array data is in caps and the input is in lower case. There are no spurious spaces. Here are the results:
Code Name
0 River Branch Foundation = not found
X RIVER BRANCH FOUNDATION = found - in array as caps
The formula:
=IF(ISNA(VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3053,2,FALSE)),0,VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3054,2,FALSE))
View 4 Replies
View Related
Aug 25, 2013
I have an issue with case sensitive while creating forms in Excel VBA. When i compared the text "EXCEL" and "excel" it showing as both the words are not equal/same.
Is there any code to compare the words/inputs without case sensitive.
View 2 Replies
View Related
Dec 8, 2013
I'm looking for a way of keeping case sensitive data in a range of cells, before using Data Consolidate, which when merged afterwards, Consolidate removes the case sensitivity and combines the quantities into one.
Check out this simple table as an example >
A
B
C
D
E
F
G
H
1
TEXT
QTY
TEXT
QTY
TEXT
QTY
TEXT
QTY
2
and
3
and
2
and
10
and
5
[Code] .........
Cells A1-B7 and C1-D7 are two sets of original data, before consolidation.
Cells E1-F4 are the result of applying a Data Consolidate operation to the A1-B7 and C1-D7 ranges - note how the merged result ignores the case sensitive condition in the original ranges.
Cells G1-H7 is the post-consolidation result that I'm looking for, where the original text case is maintained.
View 3 Replies
View Related
Aug 26, 2009
I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
View 7 Replies
View Related
May 8, 2008
Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
View 6 Replies
View Related
Jun 3, 2009
I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
View 9 Replies
View Related
Apr 19, 2012
-I have two columns
-In column "A" are listed values in black and red font color
-I need a SUM of values inside of column "B" where in the same row in column "A" value is written in red color.
Example:
AB15,00060,0000Result:35,00070,000068,000025,000050,00002,00008,00006,000018,0000
How to make this automatically at larger tables?
View 9 Replies
View Related
Jan 16, 2007
I had to crash Excel because it had got itself into a pickle - run out of memory or something anyway it was chasing itself up it's own backside.
I rebooted to restore memory and ever since then my Context sensitive auto complete on VB statements, subroutines and functions has vanished and I can't figure out how to get it back.
How to restore this as I have some seriously complicated routines that are MUCH slower to code if I don't have this option.
View 9 Replies
View Related
Apr 22, 2009
I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
View 14 Replies
View Related
Jun 20, 2008
When I use a simple formula such as:
=upper(a1)
that will obviously change whatever is in a1 to Upper Case - but it will put it in the cell that holds the formula.
What I want to know is:
Is there any way I can format the cell to run the formula when the information has been pasted into the spreadsheet
View 9 Replies
View Related
Nov 28, 2013
I have a long list of data (around 10,000 rows) with one column Client Sensitive (Column A see below) that needs to be changed before using in a pivot so the client won't see this data.
At the moment I'm using a formula but happy to use VBA for this as the rest of the report updates with VBA.
The formula that works but takes far too long is
=IF(COUNTIF($A$1:$A2,$A2)>1,VLOOKUP($A2,$A:$R,18,FALSE),MAX($R$1:$R1)+1)
Where column A is the sensitve data (and the unique identifier) and column R is the column the formula is entered.
The below is what I want, the column R I now use in a pivot to get the results I want then I can hide that column and delete Column A so no Client Sensitve data is with the spreadsheet.
However as I've said it takes far too long to calculate down 10,000 rows..
Column A
Column R
A12345
1
[Code] .....
View 4 Replies
View Related
Nov 9, 2011
What I want is to make date formatted cells change color based on a parameter related to the date. For example, turn to red 7 days before the date in the field, and back to green when the date has passed, or when it is changed (updated).
It's basicaly a payment reminder, so if this is not exactly possible, are there any other ways to do it? Maybe the cell turns red on a predetermined date each month, and back to green on another date?
View 3 Replies
View Related
Jul 20, 2006
For the following code, I'm getting the " Case without Select Case" error (On Case 3 to 5...assuming more are wrong too, but debug can't get there yet). I thought I had it right, obviously don't. Can anyone spot how my code is wrong? ....
View 9 Replies
View Related
May 15, 2007
I'm trying to compare two range values within a macro to see if they match...if they do/don't I capture and write some other data.
One list is lowercase, while the other list is UPPERCASE.
My current macro needs them to be in the same case because I'm using the following to compare:
If VPNID.Value = BuildHRName Then
How can I change the format of one of the lists to UPPERCASE or lowercase so that I am comparing apples to apples?
View 9 Replies
View Related
Jan 13, 2009
in sheet 2 cell g16 i need it to count how many times "horse racing" appears in sheet 1 column m and also for it to count how many "y" are in column g of sheet 1 and return something like
4 from 4
this example is taken from the attached sheet
basically it needs to count the yesses in column g that correspond to the horse racing in column n
so there could be 7 instances of horse racing ( 4 in this example ) and count the y's in column g which could be 2 ( 4 in this example ).
View 6 Replies
View Related
Nov 17, 2009
if there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized
The text will vary in length and content... so he wants to see how we can change:
"This Is Strange" to "tHIS iS sTRANGE"
"THIS IS STRANGE" to "this is strange"
"this is strange" to "THIS IS STRANGE"
View 2 Replies
View Related
Dec 19, 2013
I have data over the last 170 days. Each entry has a time stamp. I would like to count how many entries there are between 7:45 am and 2:45pm. A total count across all 170 days is needed, but it would be great if I could break that down by calendar day (i do have that as a column).
Here is a sample of the data:Friday11/15/1307:08AM
Friday11/15/1307:08AM
Friday11/15/1307:16AM
Friday11/15/1307:16AM
Friday11/15/1307:46AM
Friday11/15/1307:46AM
And the sheet has dates starting in June and ending in mid December.
View 14 Replies
View Related
Nov 6, 2011
Basically I have the following (simplified to just include relevant columns
Text Code Location
FFF VAC 2400
FFF VAC 2100
FFF RGG 2400
FFF RGG 2100
BBB RGG 2400
BBB RGG 2100
BBB JAM 2400
BBB JAM 2100
FFF GOG 2400
FFF GOG 2100
There is also a pass/fail column.
I need a table which counts 12 separate stats
Passes and Fails of the following:
1: Number of GOG
2: Number of JAM
3: Number of 2400s not including 2400s already in 2 and 1
4: Number of 2100s not including those already in 2 and 1
5: Number of VAC and FFF in 3
6: Number of VAC and FFF in 4
1: I have done =SUMPRODUCT(--(=Code="GOG"),--(Pass/Fail="pass"))
2: =SUMPRODUCT(--(=Code="JAM"),--(Pass/Fail="pass"))
how to split the remaining stats instwead of having to filter manually every day
View 9 Replies
View Related
Oct 9, 2008
I want a macro that counts a column that is not empty,.i want it to put in a button.
and also i want to count the data that has empty match ....
View 9 Replies
View Related
Jul 6, 2009
I have an excel sheet (2007) with over 800 records. Column A is having some names which are repetitive and Column B with some relative prj names they are alos repetative. I need a macro which counts all the names and Prj having unique combination and put in the another sheet of same workbook. e.g.
Col A ColB
ABC PRJ1
XYZ PRJ2
ABC PRJ1
ABC PRJ6
XYZ PRJ2
ABC PRJ1
PQR PRJ3
ABC PRJ1
The result in new tab should be:
Col 1 (Header: Name) Col 2 (Header:Prj Name) Col 2 (Header:Count)
ABC PRJ1 4
ABC PRJ6 1
XYZ PRJ2 2
PQR PRJ3 1
View 9 Replies
View Related
