I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
I have a yearly running log (attached). At the bottom in cell [B88] I would like to develop a formula that gives me the number of times I ran in that specific month. Dates are in Column A and running distances are in Column B. If a distance is zero, I don't want to count it. I have attempted to solve this using the =COUNTIFS formula, but I am not able to structure it properly. Maybe =COUNTIFS is not what I should be using.
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.
I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.
I want the user to be able to choose from the list or type the drug name in.
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs 40m20secs 2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
Is there a way to do this without using a macro, but I need it to be in a macro.
Column A has a value I am calling a label, ex. ABCDEF which occurs over and over. Column B has a list of animals, many of which repeat AND will be together if they do repeat. In other words, all rows in Column B with Cows are together, occurring in consecutive rows. I need a macro that will look at each row in column C and increment +1 starting at 0. That will be concatenated with the value in Column A and pasted as a value in column C.
See the linked spreadsheet tabs for Before Macro and how it should look After Macro is run.
I want to count the number of rows in a specific column up to an empty cell and assign this value to a cell. I don't want to count the total number of rows but instead I want the number of the first group of rows.
For example, column A may have cells ranging from row 2 to 10 and then from row 12 to 20, so I only want to count the first group.
The below code counts the total which is not what i need.
Code: Sub test() Dim Mycount As Single Mycount = Application.Count(Range("A:A")) Cells(1, 4) = Mycount End Sub
I have a worksheet (SHEET1) where users would enter their ID Number and then they would hit a Submit button. The submit button would then add that ID Number into another worksheet (SHEET2) in a running list of ID Numbers. But before it is submitted, I would like to check for two things:
1. ID Number must be 9 characters long (numbers and/or letters) 2. ID Number must not already exist in SHEET2
If condition 1 is not met, then just have a message box asking user to check their entry and try again.
If condition 2 is not met, then just have a message box saying "thanks" but no addition will be made to SHEET2
I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A
Sheet 1 Column A Apples Oranges Banana Fruit Salad Fruit Smoothie
So I have a column that will have the same text in all of the cells contained within it except for one cell. Is there a formula that I can use that will automatically find and transpose the text of the one cell that is unique out of the column? I was thinking of an IF function, but the column could be up to 60 cells in length.
I have an excel worksheet that creates the total arival/departure time for workers. In the "ore lucrate" column i need a formula that checks wheater or not in column b exists text, if it does then check if cell r12 is bigger than one hour and the substract exactly one hour from it, i.e. 03:45 needs to be 02:45. However if it's not bigger than an hour then put the result as it it (r12). And finally if the adiacent cell in "ore lucrate" doesnt contain text then it should be left as the folowing 00:00.
I have a column of numbers (column A) which I'd like to compare to an adjacent column of numbers (column B) and, by row, determine if the number is larger.
If it is, I'd like to place a different colour on the cell. What is the formula I need to use to accomplish this?
Ex: Col A Col B 30,000 5,000 - A is larger than B, therefore A is formatted RED 20,000 20,000 - A is the same as B, therefore no format applied 10,000 15,000 - A is less than B, therefore A is formatted GREEN
Is there a way to make a macro convert '1' to Yes and '0' to No? I have 2 columns (F and G) in a worksheet that contain the value of 1 or 0, but I need to convert them to Yes or No. I have tried some fo the samples but they pop up a msgbox which is not required.
I am trying to create a macro that will allow a user to click a button, enter a text string that will be an email address into a field and have it added to the first empty row in column A or column C on sheet 1. I have not been able to record this with the macro recorder. The input box will be in cell E9 on sheet1.
I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
I have a datatable which has a column containing up to 25 different text codes (which are currency pairs). I need to insert a specific number (as seen in the attached example) in a second column. This number depends on the text in the first column. I suspect this is very simple but at the moment, not for me!
Set cell fill color based on number of adjacent cell
I am doing a research with the following information. In column B, I have the name of the buyer company, column B cells that contain numbers 1, 2 or 3 opposite the company name in column B depending on certain characteristics. I would like to mark the companies in column A in different colors (or some other recognizable way) as described below.
columns result col A col B col C 7 Materials Inc 3 7 Materials Inc 2 6 Rayon Inc 3 6 Rayon Inc 3 6 Rayon Inc 3 8 Commun Corp 1 8 Commun Corp 2 8 Commun Corp 3 8 Commun Corp 2 8 Commun Corp 1
I would like to have on output in column A as shown above according to the following criteria:
4: if there are only 1 in column C opposite the specific company in column B
5: if there are only 2 in column C opposite the specific company in column B
6: if there are only 3 in column C opposite the specific company in column B
7: if there are 1, 2 and 3 in column C opposite the specific company in column B
8: if there are 1 and 2 in column C opposite the specific company in column B
Any suitable function in excel or Macros that can work with the formatting, because I have tens of thousands of companies and it will take too much time to do it manually.
I've a spreadsheet and I need to find cells in Column A that contains certain text and subsequently being able to compare the data in 2 other cells (column F and Column J) and post the results of comparison in Column L. Please see the tables below:
In this table, if Range("A1") contains abc, then data in Range("f1") would be compared against Range("J1"). If F1 = J1, then Range("L1") would say "ok" otherwise "needs to check" as in Rows 2.
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
At the moment I have a formula as below:
this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.
Modifying my formula. I have attached the sample workbook below.