Let's say I have two columns: Homeless (column A) & Services (column B). Homeless responses can be either "Y" or "N". Service can be "a", "b", or "ab". Here is an example:
Homeless Service
y a
y b
n a
y ab
I was hoping to use SUMPRODUCT to calculate clients who are homeless and receiving "a" service. I want to be able to calculate those receiving "a" service even if they are receiving "b" service, so I attempted to use asterisks like I have used prior with the COUNTIF function. Here is what I thought would work:
=SUMPRODUCT((A2:A5="y")*(B2:B5="*a*"))
And I expected it to return 2, but I found that I couldn't get the SUMPRODUCT to work with asterisks. I am able to get the results I want be creating a new column to sort the services into just those who are homeless and then using COUNTIF, but I'd rather come up with a more streamlined approach.
I'm trying to convert variable length strings which are being copied from a display and loaded into an array.
I have it working fine for the majority of the data, which comes in looking like "*ABC@US" or "*AABC@US"
However, some of the data looks a bit different, particularly lacking the @ symbol. So what I end up with is
"*ABC US*ABC US*ABC US"
What I need to get to is just "ABC US" FYI the US part can be 1-5 characters.
SO... I need a way to truncate anything after the second instance of the asterisk. Haven't been able to get it to work using various trim, mid, len, left, right, etc functions.
I am trying to add a * at the end of a value in a cell, used to signify a note at the bottom of the page. However, as the cell contains a formula, excel thinks I am adding a multiplication sign instead.
Is there anyway to place a * at the end of the value, to show up in the cell as text? I would prefer not to have to paste the value of the cell and then add the *...
I am trying to save a worksheet as a txt file. The worksheet has a range of data with a variable amount of rows in column A only. It generally will look like:
where each row of data is in a single cell.Whenever I save it, however, it adds extra tab deliminations in each row after the data, and it adds rows with no data (sometimes just 1, sometimes many).
I am positive that no cells in columns B-n have data in them (even just a space) and no rows after the last intended have data.
Why are these extra rows and columns being inserted? How can I save a txt file that literally has no other spaces or rows or deliminations other than what's intended?
I have an input box for the user to type in a pin number to identify themselves, I would like to make it so that what is seen in the input box is the * symbol as in most applications. I have sussed out how to do it with a text box in a form but if I use that it would mean a major rewrite of my code,
I have data in column "A" that contains an asterisk at the end of the cell value. The problem is, this asterisk can be any number of spaces away from the value that I want. remove this character from the cells? Data is copied from another program and the number of rows can be different from day to day.
I am trying to open up an asterisk delimited file then shrink the data, then after I want to save it back as an asterisk delimited text file. How would this be done in vba, as well as manually?
I'm working again with a large Inventory Database dump into a workbook and in the past the company appended the * character to designate an updated price on an item(column C). Its rocking my world to sort through things with this character as you can tell. I'm trying to find rows that column C has a * in, cut, and paste them into another sheet called "Updated". I would even accept just how I'd do a simple "find/replace all" on that character and then I can just update my macro and be on my way Anyway this is the macro thus far and as you can see it would just cut all rows and paste them to "Updated".
Sub Updated() With Worksheets(1).Columns("C") Set c = .Find("*", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Cut Destination:=Worksheets("Updated"). Range("A" & Worksheets("Updated").Range("A65536").End(xlUp).Row + 1) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With End Sub
I am trying to use the following formula: =SUMPRODUCT('Raw Data'!$B$2:$B$10000=$A$1)
The reason why I am using sumproduct is b/c I have to add more to this formula. In column B on the Raw Data sheet there are years listed. The years are formatted as text. Is there a way to have this formula work without having to change the format of column B?
I need to have the color of cell A2 change when I enter an asterisk in cell A1 to denote a critical dimension on an inspection form.
I used conditional formatting to change cell A1 orange when an asterisk is entered. Is there a way to tell cell A2 to look and see if there is an asterisk in A1 and if so change it's color?
I'd like to compare two columns of data and highlight where a value doesn't occurr in both columns. The problem I have is that one of the columns uses * around the text to ensure it catches all variations of the value.
I have the following formula which would work if the data was exactly the same.
=COUNTIF(lst2,value)=0
However, these *'s are meaning it never works.
Is there a way to make it so that if the values with the *'s are not found in the 1st column of data, conditional formatting is activated?
I have an ad hoc txt file that I import into Excel and use text to columns. The problem is some of the amounts have an asterisk in it such as: 10,412 *
I tried to use the find and replace, but the "*" is considered a wildcard and instead of changing the field to just 10,412, it deletes the entire amount.
Is there a macro that I can use to get around this? The amounts are listed in column G & H
I am trying to run a routine in VBA that will Search for a Part-No. that normally contains asterisks. It is giving me bad positive Search results due to the coding looking at the (*) asterisk's as a wildcard character.
The following Part-No.'s are located in cells (C11:C16): 9L3Z*17757*BCP 6C3Z*16138*AACP AE5Z*16138*B AE5Z*17E811*CF AE5Z*17E811*F AE5Z*17E810*F
Part-No. to be Searched for: AE5Z*17E811*F
When the below code is run, the incorrect Part-No. 'AE5Z*17E811*CF' is found in cell (C14) rather than the correct Part-No. found in Cell (C15).
Code: 'The following 2 lines are defaulted for ease of coding pNumb = "AE5Z*17E811*F" Range("C11", "C16").Select
Sub DeleteRowsBasedOnCriteria() 'Assumes the list has a heading. Dim cl As Range For Each cl In Range("A6", Range("A65536").End(xlUp)) If cl.Value <> "=~*" Then cl.EntireRow.Delete End If Next cl End Sub
but it is deleting every row, I am not sure what is wrong? starting in cell A6 if cell does not start with *, then delete entire row, next cell
that is the logic im seeing...ive tried a few o ther combinations like "~*" etc...
Y column is the text column to find the Evaporative cooler, the Q is another column where i have write the text out at AM5, N is a number field. The only bracket that is not working is the Y. Can you please let me know if I did anything wrong to it?
There are 3 types of Evaporative cooler, 1) Evaporative cooler - phoenix, 2) Evaporative cooler - Cal, 3) Evaporative cooler - Brisa. I wondered if I can use "*" to select all of the Evaporative cooler, instead of listing all three.
I recently had a lot of help using the sumproduct formula with isnumber datevalue added to it. I now want to do the same but rather than finding numbers, i want it to find text.
In my attached example. You can see that Jerry A has received three applciations, but 2 of them have been cancelled. How can i add the formula where it would only count Jerry A's applications, but where they have been cancelled i.e. the formula would show 2 not 3.
I'm using this sumproduct and it works until a cell has text. =SUMPRODUCT(($C7:$C16=$C$2)*($D7:F16))
I've tried =SUMPRODUCT(--($C7:$C16=$C$2),($D7:F16)) but got the #Value! error because of the D7:F16 range. If I changed it to become F7:F16, then it works. But...my original formula adds all values giving me a YTD result. If I changed the range to be only F7:F16, then all I get is just that column's data. I have January to December's data across the columns from D to F. Any ideas on keeping my D to F range? I've tried Transpose but it didn't work either.
=SUMPRODUCT((B7:B9=$A1)*(E7:E9>=0),"CAP EXCEEDED","CAP Not breached")
B7:B9 = text and numerical values in format Year 1, Year 2 etc E7: E9 = numerical values
what I am trying to achieve is if YEAR 1 for example is equal to cell A1 and the value in column E is greater than 0 then return statement "CAP Exceeded else return the statement CAP not breached.
The below table is a small extract from my data. I want the formula in C3 to return the contents of A4, that is, where Amount = D3, Policy = E3, Status A3. The following formula returns 0, because I'm trying to return a text string rather than a numerical value:....
Tring to get the following spread sheet to calc column C using two criteria Criteria 1 Column D must contain HC Investor anywhere Criteria 2 Column A must be less than Reimbrusement - 11 The following formula in G2 appears to have an error in criteria 1 ....
I am having great difficulties in finishing this formula off. It is a sumproduct containg many criterias, however one of them is to only add up a certain coloumn if another column begins with L&L...
The coloumd contains L&L and a number, but the number changes so i need the number to be a wildcard. I have tried ="L&L???" but this does not work.
Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria.
If recently learned how to use the sumproduct function as a lookup for multiple criteria and it was working great, but I realize now all the values that I was returning were numbers. Is there a way to use the sumproduct under the same circumstances but to return a text value?
COL ACOL BCOL C A1X A2F A3G B1E B2S
I would want the lookup with conditions of ColA=A and ColB=2, so the return should be "F" =SUMPRODCUT(($A$1:$A$5="A")*($B$1:$B$5=2)*$B$1:$B$5).
Can I use the asterisk (*) wildcard to save the file in multiple PATH as long as it the file is in the master drive C:
Ex: the file now is in "C:Public" then can I use "C:Public*" so user can save file at their desire sub-folder such as "C:PublicHenry" as long as it has to be in drive C
I'm using the following code to delete rows that I don't want to include and I've ran into some more things that need to be deleted...
For lLoop = RngCol.Rows.Count To 2 Step -1 Select Case RngCol(lLoop, 1) Case " Date:", "Skill:", "Agent Name", "~*", "*Train*" RngCol(lLoop, 1).EntireRow.Delete End Select Next lLoop
An example of "~*" would be: ***SICARII*** An example of "*Train*" would be: Ozgrid Train1
It's not recognizing these new cases. Do I have to utilize FIND? (since CTRL+F does work with the given cases)
