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 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'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...

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 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....

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...

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 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 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...

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'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...

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 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 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","")) ...

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...

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...

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...

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 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'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 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....

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...

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 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...

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...

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.

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

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 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 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 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),...

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...

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?

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 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 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 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 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 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...

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?

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...

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 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 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...

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 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 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'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...

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 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...