I want to count the number of times partial text strings occur in a cell adjacent to another cell containing specific text.
A B
FB Milton v Town PHOTO
BS Fairfax v South
BS North v Town
BS Milton v South PHOTO
FB North v Milton PHOTO
FB Milton v South
I'm looking for the number of times "FB" and "Milton" occur in Column A when "PHOTO" also appears in Column B in the same row. From numerous searches I've tried figuring out VLOOKUP, DCOUNTA, etc.
I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:
A1 - Apple A2 - Banana A3 - Apple A4 - Banana
B1 - 3 B2 - 2 B3 - 1 B4 - 1
I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.
One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to: AE3563636D145C2501AP AE3563636D175C2501AP AE3563636D210C2501AP
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.
Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
How do I count unique values in Column A only if the values in Column B match?
A B 1 a 2 a 1 a 4 b 5 b 1 b
The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.
The solution to a challenge here does almost exactly what I need except it doesn't manage partial matches.
Function MatchColors(strValue As String, rngList As Range) As String Dim regEx, Matches, i, strResult, bFlag
'Create and set the parameters for the regular expressions object Set regEx = CreateObject("vbscript.regexp") regEx.Global = True regEx.IgnoreCase = True
I'm trying to sift through 10000+ rows of results, and I'd like to know if there's a count formula to determine how many times I have matches in one column (name) and mismatches in another column (ID #)
Profile ID |Name | License # 12345 |Debra Nelson |12345678 12345 |Debra Nelson |23456789
I want to count how many times theres a match in the 1st or 2nd column (profile ID, name - those two columns should match) and a mismatch in the 3rd column (License #). I'm not sure if this can be done with a formula, like a COUNTIF, or not.
I have data (coming straight out of another system, so whilst is seems messy, its the easiest way to have it in Excel).... where there are a number of rows (one for each employee) - then every column represents a date in the month (so there are about 30 columns)....the cells contain a number of hours worked by that employee for that day.
I have figured out how many hours they worked for each day of the week, but my client now wants to know:
for each employee - how many Mondays did they work?- how many Tuesdays? and so on
I am happy to figure out the day of the week for the first date in the month and then manual set up another column heading above the date that tells me what day it is - but I feel that Excel may actually be able to work out what day of the month it is - although the dates come across from the other system as text - April 26, 2006 etc...
So I would like a summary in my sheet that has column headings Mon, Tues, Wed, Thurs, Fri, Sat, Sun and then for each row for each employee - I would like the number of days that employee worked for the month by these Days of the Week.
I have looked at Count if - but I need to be able to select the range of cells to count, and also the range of cells to evaluate for the criteria and I cant figure it out!
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 have two columns, some of the cells in these columns contain more than one value separated by a space. Example:
Col A Col B 1.99 1.69 39.95 6 119.94 29.99 6 149.94 135 250 135.00 250.00 11.6 11.60
What I am trying to achieve is to test that the values in column A match those in column B and have a TRUE/FALSE response in column C. At present i'm just using =a=b, however I want the formula to give me a TRUE response for the third and fourth lines in my example, as I want it to ignore the zero after the decimal place, at present it gives me FALSE.
So the results I want to see in col C for the above example are: FALSE FALSE TRUE TRUE
news1 = "new" news2 = "polygon" Dim countie As Integer For buddie = 2 To b1.UsedRange.Rows.Count 'If b1. Cells(buddie, 1).Value Like "new*polygon" Then If b1.Cells(buddie, 1).Value Like news1*news2 Then countie = countie + 1 n1.Cells(buddie, 10).Value = "test" End If Next buddie
I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.
How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.
b. If i have a value in the cell: /new/blahblah/anycra/polygon how do i split it so as to assign two variables to have 'new' and 'polygon'.
I want to search for a word in column A and when I find it I want to copy it to column B. Column A is a description that can be 6 or 7 words long. Column B is a single word.
Example:
Col A Engine Kit, V-8, 306, forged. I need to copy the word Kit to column B.
I need a formula to run down a column DCapture.JPG (starting at 142), when it finds the last entered value it needs to display the corresponding value from column J into cell AA21.
If you see the attached photo, the last entered data in column D would be 1, AA21 would be saying -30 (J205)
Im trying to make an excel spreadsheet for work for working out how many of each product and individual staff member has sold
I have 2 columns one with staff and one with what they sold e.g.
Staff Member - Sales Staff 1 - prod A Staff 2 - prod B Staff 3 - prod A Staff 2 - prod C Staff 1 - prod A
and have another table, the one that i want to populate, that looks like this
Staff 1 prod A = prod B = prod C =
Staff 2 prod A = prod B = prod C =
etc
I cant seem to find a function that will work out how many of prod A staff 1 has sold and how many prod B he has sold etc
i have tried things like countif but i cant seem to get them to link the staff member and sales boxes together, the only way i can think of is to write down an equation for each line, but i have hundreds of sales and that would takes ages
I have a spreadsheet which has a source worksheet, containing 2 columns with thousands of client numbers and account numbers. I have a destination worksheet which contains certain account numbers which are featured in the source sheet.
What I need to do is pull the matching client numbers across from the source sheet into the destination sheet. An example is attached.
I have two sets of data from columns A:N (O is blank) & P:AC. Column A & P are account numbers. I want to compare columns A & P for exact matches, there will never be any duplicates in either of these columns by themselves. If there is a match I would like that entire row to align, if there is no match I would like a row to be inserted. I have attached a copy of a worksheet of what I am looking to have done.
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).
Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:
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!
I have two sheets that need to have unique partial matches aligned side by side. My first sheet is my database. The righter most column of this sheet contains a unique combination of letters and numbers.
My second sheet contains a single column which I export each month from our CMS and is a list of URLs. Within each of these URLs appears the unique set of letters and numbers from sheet 1 (and only appears once).
There are about thrice as many URLs in sheet 2 as there are entries in my sheet 1 database.
Ideally, I would like the URL from sheet 2 that contains the unique combination from sheet 1 to be pasted in the cell immediately to the right of the given unique combination.
However, I will settle for a list of trues and falses next to the URLs in sheet two that I can erase the falses, sort and paste next to the sorted list from sheet 1.
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.
Cell H1 has a variable string of references for eg "FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
