I have a column that has almost 500 rows. Each cell has " DELETED", "OPEN" or "CLOSED" written in the cell. I need to know how to calculate how many times "OPEN" is listed throughout the column.
View 4 RepliesI am trying to find the most occurances in one column of the same word (List of Suppliers) and return how many times that name cropped up, would like to use a single formula to do so and not have another column with numbers in it if possible.
View 9 Replies View RelatedHow can I add the number of remarks to the number classes based on their row?
I have a TALLY SHEET which auto computes the number of occurances of each classes
and remarks…can someone help me how to add the class and remarks? In this example
you can see that CLASS A occurred 3x ,YES remarks occurred 2x and NO remarks occurred 1x for A class…
how can I add the occurances of YES and NO remarks to A class?
This should be the output…Remarks are being added according to the class they belong
ABEYESNONot Applicable
643242
Is there a way to find the minimum value in column B that corresponds to the two occurances of "Yes" in column A.
Column A Column B
NO 1
NO 4
NO 7
YES 6
NO 3
NO 9
Yes 2
[Note: the numbers are in column B.]
I basically want to return the # 3 from Column B.
I have used this formula to get the last value in a column:
=LOOKUP(9.99999999999999E+307,Items!A:A)
That seems to be the most elegant formula that I have seen at this Message Board.
But what about the most elegant formula to get the last word or value or error or TRUE/FALSE in a column?
I have used this formula (entering it with Ctrl + Shift + Enter):
{=OFFSET(Items!A1,MAX(ROW(Items!A:A)*(Items!A:A""))-1,0)}
How many of you know other more elegant or efficient formulas?
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
I want to say if the word 'suitcase' is in column W (any row), place the word 'suitcase' in column Z, same row.
View 2 Replies View RelatedI have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder) The names of the templates are: "Standard" and "Other". I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done. One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
View 1 Replies View RelatedI have on the first tab a screen where you can fill in the name of the .doc or .pdf file.
That works fine, but now I would like that when I fill in a number and click on test, it opens te .doc and .pdf file that is attached for that number.
If you take a look at the pdf file you will see a second and a third tab.
First column is the number they need to fill in and the second column is the name of the .doc or .pdf that needs to be opened with that specific number.
How to link it and open both files for that specific number.
But not every number has a .doc as well as a .pdf.
How can I count the number of cells in a range which contain a specific string?
I'm trying to use SUMPRODUCT to no avail:
=SUMPRODUCT(--SEARCH("RSC",I3:I3000)>0)
I'd like to count the number of time Y or Yes appears in a column. In a different cell I would like assistance in figuring out what formula could calculate the percentage of attendance based on the total Y's or Yes's.
View 3 Replies View RelatedI've imported a lot of data and it looks like this:
blahblahblahWord 1 Word 2 Word 3
First, I want to erase the "blahblahblah"
Second, I want Word 2 to be bumped over to the next column and Word 3 to the column after that.
Is there a formula to do this? There's so many rows of data that it will take a long time to do it manually.
OK I have included the spreadsheet I am working with and I will try really hard to explain exactly what i want and where my problems lie, so please bear with me.
The work book is separated by months. The individual spreadsheets can have multiple entries in each cell, I have a code running to automatically pop up a comment text box for each entry into that cell to separate each occurrence.
Now I have a separate sheet that will be showing how many times each item has a "hit" or occurrence for the entire month. That's where I have the problem. I don't want to go and count every time i made an entry, excel should do that. The problem I am coming up with is that I can only get it to calculate the one occurrence per cell which does me no good if there were three separate occurrences in that cell.
I am trying to use an SUM array to count occurances of a given criteria.
Here is an example of one that works for me.
I would like to take the data in the first table and put it into the format of the data in the seconde table "Using EXCEL Functions" i.e. Not VBA code/MACRO. Note there are multiple occurances of some names in the first table with different fruits.
Name Fruit
MarkOranges
SteveApples
RoyPears
TedTomatoes
JohnApples
JohnPears
SteveTomatoes
RoyOranges
AlbertOranges
MarkPears
RoscoeTomatoes
Is it not possible to use entire columns references when you're looking up one value and wanting it to return multiple corresponding values? For example, my formula seems to work fine when I use something like:
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$10,ROW($A$1:$A$7)),ROW(2:2)),2)
(with an array)
But if I change $A$1:$B$7 to simply $A:$B (and the rest to straight column references) it gives me a "NUM! error. I need my range to reference a dynamic range because values will be added to the table.
Basically, I need a formula that will return multiple occurances of the same lookup value.
I need to produce a summary of the monthly spend with parts suppliers for a number of vehicles, with each vehicle having it's own worksheet. The suppliers' names are in column C with the costs in column H.
So what I need to do is sum the results of column H on each worksheet when the supplier's name (which is on the same row) matches "Triple 7" and it's in the same month. The idea is to produce a sheet which displays the total spend each month with our suppliers.
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
I am creating a review sheet/skill matrix using excel and need to be able to convert a particular selection to a number in the adjacent cell to work out the users efficiency (long story that i wont bore you with)
So basically i have a question/statement in cell A and a drop down answer menu in cell B containing up to 5 possible answers (very poor, poor, ok, good, very good). In cell C i would like to insert a formula that allocates a number dependant on the selection in cell B. So if the user selects very poor in cell B cell C states 1, if cell B has poor selected then cell C states 2 etc etc
I am trying to create a spreadsheet to keep track of an online racing league that I'm in. I have created columns for the racers, and rows for the races. What I would like to do is type in 1st, and it represent the number 13 for when I do an autosum at the bottom of that column. It would be easier for me to type in the position place, instead of the points value. The position place will always carry the name numeric value. Here is what I have so far:
Start & Park Season 1.xlsm
I'm looking for an IF function as follow:
IF Column B doesn't have the word NO or NMI or "N/A" then Column A will remain the same. Otherwise, change to column B value.
Column A
375-9323
384-803
267-6491
Column B
D
NO
A
A
NMI
N/A
I've been playing with different Macro but haven't got any luck so far.
Right now the code below reference AI on sheet BOM. However, I need it to be either referenced by being the last filled row on sheet BOM or by having the word "rounded" on row 4 --what which over column on row 4 that has the word "rounded".
Current code:
Range("J2").Select If Sheets("BOM").Range("C4") = "" Then
ActiveCell.FormulaR1C1 = "=BOM!R[3]C[25]"
Else
ActiveCell.FormulaR1C1 = "=BOM!R[4]C[25]"
End If
I have a column with codes coming after a # symbol and want to extract just the code from the cell. The code could be 2 or 4 characters in length. Some examples:
#AA abcdef
From this I would want the formula to return #AA.
#ABCD qwerty
From this, I would want the formula to return #ABCD.
The #Code should but may not always be the first word in the cell, so it needs to find the # symbol and then take the next x characters until a space is found. Note that the reason code may be the only text in the field, so a space may not be there at all. For instance,
#RR
Should just return the #RR.
Edit: This actually has become more complex now. The cell could be
#AA{text}
And for that, I would just want the #AA.
The formula would need to begin at the # and return just the next x capital letters.
Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
Is there a way of getting the first three character of every word or number in a cell ( seperated by spaces) ?
for example in cell E2 I have
BASE 150 WALL COLOUR GREEN
and would like it to read
BAS150WALCOLGRE
I have a approx a 1000 rows that I would like to do this with.
write a macro that search first Row and if its finds the word "Date" then the whole column should select and change the Number formatting to Date, and if it finds the word "Time" it should change the formatting for the whole column to Time.
Below is the format of Table where it should change the formatting of Date, Start Time and End Time.
Date
OrderNumber
Start Time
Status
[Code].....
I have a spreadsheet where I want to record the VO2max value (a number indicating the maximum volume of oxygen a person can utilise) of a person.
The VO2max is just a number, normally between 30-60.
I then need excel to look up the VO2max value (i.e. “35”) and give that number a predetermined inputted value name in a different cell (i.e. “low” or “fair”, “good”, “very good” etc etc).
However, as VO2max is also affected by age and *** I also have a column for the participants age and *** and I want excel to use that to make the value name.
E.g. in a 24 year old male a VO2max of 32 is “low”
But, in a 42 year old female a VO2max of 32 would need to be classed “”moderate”
Any ideas how this can be done? can it be done?
I could post the table containing the age range, values and names if I knew how to post an excel sheet on here.
On sheet 2 in cell C5 I need it to find it from sheet 1 referring it from the word "Drinks 1" thats in Q1 and so on along the row
View 9 Replies View Related