I am trying to highlight data by row based on an a ref number. The system I pull the report from creates a new event and thus new row item for each action. So every time it breaches or is achieved a new line is created and I am only...

I am using the following formula in an Excel sheet, that performs the SUM in a range of cells from another sheet (ITEMS CONTROL): =SUMIF('ITEMS CONTROL'!$B$2:$B$20000,B$1,'ITEMS CONTROL'!$C$2:$C$20000) My issue is that when inserting rows in the sheet ITEMS CONTROL, the formula changes as follows: After inserting 18 rows: =SUMIF('ITEMS CONTROL'!$B$20:$B$20018,B$1,'ITEMS...

I am trying to get an IF statement containing four conditions to result in one answer (or blank). Here is my formula: =IF($L2=1,IF(AND($H2>=50,$O2>=5,$O2<8),"A: Priority 2","")) ...

My Excel will be populated using query now the below is the data set which is coming! COL A COL B COL C COL D Claims on Public Sector Entities in Pakistan 0% 0 Claims on Public Sector Entities in Pakistan 1 20% 0 Claims on Public Sector Entities in...

We have a table with values like this: I need a formula for cell D1 to count rows that have in column A value "one" and column B is not empty. For example: in first row A1==one but B1 is empty; (that doesn't count) in second row A2==one and B2...

I have a giant Excel file that contains a time column and a data column. In two new columns I'd like to pull the time and the corresponding data (same row) when a new second begins based on the time column's value. The number of rows per second is not...

I'm trying to build a spreadsheet to help automate points scoring for an office F1 fantasy league we have. I've attached an example data set, but basically I need to search a range, then count how many times the constructor appears in the numbered positions (discounting R, D as they've...

I'm creating a revenue model on a simple spreadsheet. On the top, I have a variable cell (C5) that is the "number of weeks between transactions". Below that, I have a simple weekly revenue model. Problem: What I would like is to be able to tweak the fields in yellow...

I have data, for example: John Doe MD 1 Ben Doe PA Cal Doe MD 1 Drum Doe PA Egg Doe NP Fun Doe MD 1 So everywhere there's an MD I have an IF condition assigning the number 1. But I want to isolate and pull all the names...

I currently use conditional formatting on columns M and N on a spreadsheet to fill duplicate number values using the formula: =COUNTIF($M1:$N1,M1)>1 I was asked by my boss if I could only conditionally format columns M and N if a specific text name in column G appears on that same...

I'm trying to count the number of times two statements occur. The problem is that if the first statement occurs then it won't check for the second. Maybe if easier if I explain like this: If statement1= true then count = 1 and move next else if statement2= true count...

I need to create formatting for numbers while keeping their actual value: If a negative is number entered, the minus sign needs to appear on the right-hand side of the number. If a positive number is entered, the plus sign needs to be on the right-hand side of the number...

I have a worksheet with 65000 rows. The first cell in each row is from a list of 1,500 values. For example: PartNumber1|Application1 PartNumber1|Application2 PartNumber1|Application3 PartNumber2|application1 Partnumber2|Application2 PartNumber3|Application1 PartNumber3|Application2 PartNumber3|Application3 Etc... I need to get an output like the following PartNumber1,Application1,application2,application3 PartNumber2,Application1,Application2 PartNumber3,Application1,Application2,Application3 ....etc Now, in Excel, I can create...

I need a formula on Excel which would deduct the multiple of 20 from a given number. For example: 2 would give you 18, 10 would give 10, 23 would give 17, 118 would give 2, 321 would give 19....

I have a string which looks like this 20141007023617. And I would like to make it look like this 2014-10-07T02:36:17Z. The T and Z are very important for me. So I would like to insert them as well. So a recap, A1 holds this string 20141007023617 B1 should hold this...

I have an invoice, where there are two columns: Rate (Column H) and Quantity (Column I). And there is Amount (Column J). I need a formula so that it would automatically multiply Rate and Quantity and show it in Amount. The number of rows would be changing (added or deleted),...

Is there a way to detect what a cell use to contain before it was changed. I'm trying to perform actions based on what the previous value was in a cell. I know there's Worksheet_Change but the Target it uses is the new value.

Is it possible to change a full column of such as F01U123456 into F.01U.123.456 with an Excel formula, or would it require a macro?

I have an input with these values in different columns: First column Second Column Third column 72 54,72 35,43 63 63,70,74 41 39 32,39,49 51 From this table I need an output like this: First_output Second_output Third_output 50 54,35,43 [50,54][54][35,43] 50 70,74,41 [50,70,74][70,74][41] 50 32,49,51 [50,32,49][32,49][51] First_output I made it...

In a cell I have a string of text that contains both a Contact Name and Company Name (example in cell A2: John X. Doe, XYZ Inc.). I have successfully created a formula that uses the "_" (underscore character) that connects Company Name with ending Company Name Suffixes (i.e., _Inc.,...

I have a table in Excel with this format: the first column is Name and the second one Phone with phone number format XX YYYYYZZZZ (e.g. 12 981152989). XX is the state code, YYYYY the first part of number, and ZZZZ the second part of number. I have to do...

I want to delete text part from current column. This text part is presented in another column. To be clear here is the image: So I want to delete Model number from Title column....

I currently have the following formula to track my work schedule. Time is put down in traditional format ie. 8:00 am, and is then totaled to show hours worked as a whole number 8, for 8 hrs worked: =IF(D2="","",ROUND(IF(OR(C2="",B2=""),(D2-A2),(B2-A2)+(D2-C2))*24*4,0)/4) A=Time In , B=Lunch Out , C=Lunch In , D=Time Out....

I want a formula for count how many True/Yes/Si are in the range. If A1 is not empty i want to count. I don't know how to put the 3 parameters in the formula =IF(A1="";"";COUNTIF(A2:A5;"TRUE")) <- I need to add Yes and Si. Edit: I forgot to say that I...

I have two columns: Date and Price. I want to pick the value of the cell in the same row as today's date: I want to show "today's" price, so supposing "today" is 12 Feb, then for the above example today's price should show 18. How can I do that...

I am looking to write a statement with the following conditions: If A1="apples" then match B1 in Sheet4 column C If a match is found return the value in F1 If no match then return the value of F2. The formula will be written in D1....

I'm trying to transfer values with macro in Excel for my upcoming billiards tournament playoff table. For example let's say I have column A with names {A1,A2,A3,A4} and every name is on a different row. Then I have column B in the same sheet with according values {1,4,3,2}. Now I...

Sub UpdateFormula() Dim CurrStr As String Dim EndRow As Long On Error GoTo 0 EndRow = Range("A" & Rows.Count).End(xlUp).Row BaseStr = UCase(Range("A2").Value) Application.ScreenUpdating = False For iter = 4332 To EndRow CurrStr = UCase(Range("A" & iter).Value) result = Application.WorksheetFunction.VLookup(CurrStr, Sheets("CustAR").Range("A2:A2499"), 1, False) '=IF(ISERROR(VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)),"NotFound",VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)) Next...

I'm observing an apparent discrepancy between the results of the EXACT function and the conditional formatting tool. Two cells in Column B have the same apparent text (about 30-40 words). But the "highlight cells" tool doesn't recognize them as duplicate values. Comparing the two cells with the EXACT function returns...

I'm trying to write a program in VBA for Excel 2011 that can search a column (which column that is is determined by another variable) for the number 1 so that it knows where to start an iteration. Say that the number of the column is given by colnumvar. The...

I have journal articles which contain three issues per volume. Is there a formula to increment 1.1, 1.2, 1.3 and then jump to 2.1, 2.2, 2.3?...

I need a hint is solving Excel task. I have two columns with data (let's say column A contains a list of people I have met in May; column B a list of people I am planning to meet in June). Is there a function in Excel which will compare...

City Job London Webdesigner London Webdesigner Tokio Lawyer Tokio Teacher Buenos Aires Manager London Athlet Rome Doctor London Webdesigner I'm looking for a way to count the frequency of Webdesigner if "City" is equal to London (in same row). The result of the formula should be 3....

I have on Sheet1 18 columns: N a list where an Order Number can be selected O a Line number (as the orders have multiple line numbers associated with them) P, Q and R, I want to pull over the data associated with the Order Number and Line number entered....

|A B C D --------|--------------------------------- 1 |YEAR NAME UNITS SOLD 2 |2011 Jon 99 55 3 |2012 Jon 70 22 4 |2013 Jon 65 34 5 |2014 Jon 78 44 I would like to know how to find the year Jon sold the most (in this case the 55...

I want to write a formula in Excel to determine if EVERY cell in a row has the same value. There are five cells, for example: C2:G2 that I need to filter out only the ones with the same value, so if only one out of the five cells has...

How would I combine these together into one?: =IFERROR(VLOOKUP(B2:B11,Sheet2!A:B,2,FALSE),"No Match") =IFERROR(VLOOKUP(B3:B12,Sheet2!D:E,2,FALSE),"No Match") =IFERROR(VLOOKUP(B2:B11,Sheet2!G:H,2,FALSE),"No Match") These are just three but eventually I would need 12 in total to be joined as these are searching team names and there are 12 teams in total....

I want Excel to look up which row in column1 contains those strings in column2. When found, highlight those rows in column1. Then I can group those rows together and use them for something else. I have a big data set so I need this to be automated. Column1 Column2...

Guys I need to find specific information in text, and write them down in column. So, I have Column L with Long Description, and I have Column M with words that I need to find in Long Description. When word has been found write that word in Column N at...

I want to get rows that have "/" character at the end of Image column value. So, to make it clear here is the image: What formula I have to use to filter rows that way? ...

I have an Excel file that will be saved as an .csv file for importation into an email automation system. In a function I need to return a piece of HTML code as text. =IF(A2<0,"CHECKMARK CODE",A2) The "CHECKMARK CODE" needs to be replaced by: > < span style="font-size:16px">& #10003;</span>. For...

In sheet "Dividends" I have a table with dividends sorted by daily dates. E.g, columns A and B contain the following entries: 6/14/2015 6/13/2015 6/12/2015 0.045 6/11/2015 6/10/2015 This means that the stock paid a dividend of 0.045 on 6/12/2015. In another sheet "AdjClose", I have a table with weekly...

aaa bbb ccc D 1 5 9 # (aaa, bbb or ccc) 2 6 10 3 7 11 4 8 12 I am trying to find a formula (to go in D2) so that the number matches its column header (aaa, bbb or ccc). For example if I put 5...

I have the following values: Type Value A 5 B 6 A 3 B 7 A 9 A 11 I want to calculate the median value for Type A, and ignore values for Type B. How would I accomplish this, as there is no MEDIANIF function in Microsoft Excel? ...

I want functions which will: add +1 to cell I3 for every W, L, T, IP and R in cells from F8 to F10, so now it has to be 3 because cells F8, F9 and F10 are populated: If F11 were populated also, then it has to be 4....

Both ColumnA and ColumnB are blank except for O and R (individually). How can I count the number of times a row contains O in ColumnA and R in the same row of ColumnB together with a row that contains R in ColumnA and O in the same row of...

I have such Excel data: I want to find records in ColumnA which do not exist in ColumnB....

I'm trying to make something of a pattern recognize counting cell and calculate their probability of occurrence. Set 1 is from a database that looks like this (consists of 6+1 columns): ABABAB 1 CACACA 2 CACACA 2 CACACA 2 CACACA 1 ABABAB 1 Set 2 is what I input manually...

I have the following formula that I need adjusting and not sure what to do: =IF(TIME(8,0,0)-A1=30/1440,TIME(8,0,0),A1) If I enter 7:30 or 8:00 in cell A1 a value of 8:00 is returned. What I want to do now is also add 8:30. Which means when I enter 7:30, 8:00 or 8:30...

I'm looking for a formula that will allow me to look for a value in a table and return any information if the value exists. Either True/False or a number where it starts (like search formula). The problem is that I will be looking for a word Apple and in...

There are two tables, each with a coordinate pair: longitude and latitude, in decimal degrees (i.e, 37.23222, -121,3433). How do I match table A with its nearest coordinate pair in table B?