Testing Contents Of 2 Cells - Ignoring Partial Matches
Jul 6, 2012
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
View 4 Replies
ADVERTISEMENT
Nov 19, 2007
I have a list of about 2 million phrases. The list was created by combining two lists in all possible combinations:
a list of about 800 street names
a list of about 1000 property names
example
church street flat
church street apartment
house church street
house king's road
etc
what I would like excel to do is the following:
look at each cell and determine which of the 800 street names it contains. write that streetname into adjacent cell.
this would then leave me with the original column, where each of the 2 million cells has been assigned one of 800 streetnames.
for example:
church street flat | church street
church street apartment | church street
house church street | church street
house king's road | king's road
that's all. I think that can't be too difficult, and probably it has already been discussed here in the forum. unfortunately I did not know for which keywords to search, that is why I couldn't find the posts.
I hope somebody can help me as I need to get this sorted for work as soon as possible. All semi-automatic ways I could think of to accomplish this would take me days that I don't have.
View 9 Replies
View Related
Aug 19, 2009
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
View 9 Replies
View Related
Mar 18, 2014
how to find partial match of text in 2 different columns.
For example:
Column A:
Boston
Chicago
Los Angeles
New York
Detroit
Miami
[code].....
I want to be able to create a column that finds all the matching cities from A in B.
View 9 Replies
View Related
Aug 11, 2014
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.
View 6 Replies
View Related
Jun 30, 2006
I have two questions:
a. Check the code below:
Dim news1 As String, news2 As String
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'.
View 9 Replies
View Related
Dec 12, 2007
I need to extract partial contents of a cell.. right now in the cell it looks like
address: 9999
so I want my code to see the cell... dropp the "address: " and only retrieve 9999.
I know it's on google somewhere but I can't seem to describe it properly for the right results to come up.
View 9 Replies
View Related
Jan 2, 2014
Below is a small sample of Column A from a spreadsheet I'm working with. All cells in the column begin with a 6 digit number:
115383_BOSTON
115384_NEW_YORK
115385_PORTLAND
115402_LOS_ANGELES
115403_WACO
115010_SAINT_PAUL
115011_SAN_DIEGO
115130_WEST_NYACK
115021_CHAPEL_HILL
What formula would allow me to enter only a 6 digit number and return the entire cell contents, i.e, search for 115403 and get a return of 115403_WACO ? I have tried INDEX and MATCH, but I'm not getting the results I need.
View 7 Replies
View Related
Jan 29, 2010
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
View 4 Replies
View Related
Jul 31, 2009
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.
View 9 Replies
View Related
Oct 1, 2009
I am trying to write an Excel formula to test if there is data in pairs of cells and if there is I want to copy the entire record to another Excel spreadsheet. Example: if there is data in row 1, column A and data in row 1, Column C or row 1 column D, then copy this record to another spreadsheet; if there is data in row 1 column B and data in column C or column, then copy this record to another spreadsheet. Since there is no data in row 4, column C or column D, this row would not be copied. Row 1, row 2, row 3, and row 7 would be copied to another spreadsheet. The data can be numeric or alpha numeric.
I tried using the ISBLANK statement and can't seem to get this to work properly.
View 14 Replies
View Related
Jul 26, 2012
Basically, looking at the range E6:L19 I need to ensure that any data that is entered into any of these cells ends in either H or W, and if it doesn't flags up a warning message (which I am hoping to link in with the Worksheet_SelectionChange event)
I'm managing it for one cell, but not a whole range.
View 4 Replies
View Related
Mar 25, 2012
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))
View 4 Replies
View Related
Feb 20, 2007
I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution...
I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust).
I sort the list so that the all names that are the same (ie John Doe) are together.
A short example list looks like this:
Jeremy Apple
John Doe
John Doe
John Doe
Jimmy Kravitz
Jimmy Kravitz
In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it).
Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on.
Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...
View 9 Replies
View Related
Jan 14, 2013
I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
View 4 Replies
View Related
Aug 8, 2014
I want to count back the last 6 cells in column S which has data (ignoring any blank cells) and add them up.
My data is ranging betwen S62:S143 (with S144 being the cell that I want to show the total for the last 6 cells with data). I have column headings and various other bits of data from S1:S61 that I want to exclude completely but need to keep for historical purposes.
Here is an example of some of the data in column S:
56
BLANK CELL
BLANK CELL
BLANK CELL
45
34
BLANK CELL
BLANK CELL
37
BLANK CELL
BLANK CELL
42
BLANK CELL
46
49
BLANK CELL
BLANK CELL
36
35
This is my formula so far:
=SUM(OFFSET(S62,0,0,COUNTA(S62:S143)))
I have tried adding a negative and positive number on the end of the COUNTA formula but all it does is count all the cells within that given heigh range, not a specific number of cells with data.
I don't specifically need to use SUM. As long as I get a total.
View 3 Replies
View Related
Aug 20, 2009
I have a range of cells (B6:M6) and I want to count how many have data. My problem is that all of the cells reference cells on another worksheet. Every cell starts out like =Bethlehem!B6. I want to ignore cells that have a reference in it when I count. I know how to do it if you want to ignore these cells as follows: =countif(b6:m6,"<> 0"). Those cell references return a 0 if they have no data in them. However, a user might actually enter a 0 in the referenced cell and I need to count that one. The above formula would exclude it.
View 4 Replies
View Related
Aug 10, 2009
I've got four columns of data. The first column contains pricing for a bunch of products from our company. The other three columns contain pricing for three other competitors. So for example:
Product | Our Price | Comp1 Price | Comp2 Price | Comp3 Price
Gloves | $4.59 | $5.00 |$6.00 | $3.56
Hats | $5.00 | | | $4.59
In column G right after Comp3 Price I have an IF statement that says if Comp1 Price is less than Comp2 Price, Comp3 Price, and Our Price, to print "Comp3" in that cell. If it is not less than the comparable data, then check Comp2 Price then Comp3 price in the same fashion. If Comp1, Comp2, or Comp3 is not less than Our Price then print "My Company Name." This will allow me to see who has the lowest price for that product and also tell me if that lowest price is lower than our price.
My problem is that I can't get it to work out so Excel ignores blank cells. So for example, Comp3 has the lowest price amongst our competitors for hats and is also lower than ours. But when I check for Comp3 price being lower than Comp1 and Comp2, it comes back as negative because Excel sees those cells as zeroes even though they're blank. My formula is:
=IF(AND(C2<D2,C2<E2,C2<B2),"COMP1",IF(AND(D2<E2,D2<C2,D2<B2),"COMP2",IF(AND(E2<C2,E2<D2,E2<B2),"COMP 3","MYCOMPANY")))
This would do exactly what I need Excel to do if it would ignore the blank cells. The problem is that Comp3 has pricing for hats because they offer hats, but comp1 and Comp2 don't offer hats at all, so naturally Comp3 has the lowest price; however, according to Excel Comp1 and Comp2 have the lowest price because the cells are blank and counted as zeroes.
So, does anyone know how to work this out so Comp3 will be counted as the lowest price for hats, ignoring the blank cells of Comp1 and Comp2?
View 10 Replies
View Related
Feb 5, 2010
I am trying to return the criteria MET, NOT MET & EXCEEDS. This works when each cell contains a number, but when one is blank the formula below returns "EXCEEDS" I'm guessing this is because is sees a blank as zero.
=IF(BN102<$BJ102,"NOT MET",IF(BN102>$BJ102,"EXCEEDS",IF(BN102=$BJ102,"MET")))
I've tried using this alternative but it still returns the same.
=IF(BO102<$BJ102,"NOT MET",IF(BO102>$BJ102,"EXCEEDS",IF(BO102=$BJ102,"MET",IF(ISBLANK(B102)," ",""))))
Basically I need to know how to make excel ignore the blank cell and not see it as zero thus returning "EXCEEDS". Although the cell values are integers they are derived from another cell using the following formula, I'm not sure if this makes any difference
=IF(ISERROR(VLOOKUP(BO11,$BH$63:$BI$87,2,FALSE)),"",(VLOOKUP(BO11,$BH$63:$BI$87,2,FALSE)))
View 3 Replies
View Related
Oct 18, 2009
I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.
This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.
View 9 Replies
View Related
Aug 18, 2014
I have created a drop down cell based on the cells in the first row of a particular table. Now when i select a particular cell from the drop down i want to fetch all the values in the respective column of the selected cell on to a different group of cells. While fetching the data to those group of cells, i want to ignore all the null, blank cells. All this actions shall be automated and shall happen simultaneously on selecting the drop down.
View 1 Replies
View Related
Sep 5, 2009
Using formula rather than VB, I would like to calculate the number of blank cells that appear in a column BEFORE the first active cell but exclude any blank cells that appear after the first active cell. To elaborate, I have sheets that contain the days of each month and I need to exclude for other calculation purposes, the number of days (cells) where no entry of data has been input at the beginning of a month, NOT after the first data entry. ie. September has 30 days.
The first data entry is the 10th and there are no entries (thus far) after the 10th, the result that I seek, will be 20 (although only one cell has data), being the days left in the month AFTER the first entry. I have looked at COUNT functions but cannot find an solution. Perhaps it's not possible using basic formula?
View 4 Replies
View Related
Oct 24, 2011
I am trying to get a rolling 8wk avg of a large group of data. I am trying to take the avg of 8 vlookups:
=AVERAGE(VLOOKUP($A$9,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-7,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-14,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-21,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-28,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-35,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-42,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-49,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE))
However, in some cases, the cell to be looked-up may be blank. Using the formula above, the result of these vlookups is "0". I want to take the average of these vlookups excluding the blanks from the 'Raw Data' sheet.
View 6 Replies
View Related
Dec 28, 2007
I have a workbook which has data in column A. I also have data in column I. What im trying to do is copy and paste the data from column I to A but if the cell in column I is blank I need that ignored and the data thats in column A at pres kept.
View 9 Replies
View Related
Jul 16, 2008
=IF(Z2="","",Z2&","&IF(AA2="","",AA2&",")&IF(AB2="","",AB2&",")&IF(AC2="","",AC2&","))
I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.
View 9 Replies
View Related
Nov 24, 2008
I don't know if this is best in a formula or macro but what I am trying to do is create a list of data with no empty cells from a list of data with empty cells.
What I want to say is if A1 is blank move to A2, if it's not return the value in A1 to B1. If A1 and A2 are blank then return the value in A3 to B1 and so on. Generally it's a sort function but I want it to happen automatcially and lkeep all my values in the original order
Original Version:
Date 1/4 Value 1/4
23/02/199913.506
5/05/199911.901
14/05/199912.152
24/05/199911.607
7/06/199911.187
29/07/199911.828
2/09/199910.473
6/09/199910.429
Desired outcome:
Date 1/4 Value 1/4
23/02/199913.506
5/05/199911.901
14/05/199912.152
24/05/199911.607
7/06/199911.187
29/07/199911.828
2/09/199910.473
6/09/199910.429
View 9 Replies
View Related
Sep 30, 2009
I am trying to add up wins and loses for individuals on a team. 6 players
There are 3 games per match and one match a week for 15 weeks. One sheet per week.
The players are inputted randomly each week. And the scores are entered after the match.
The following is a part of the formula that I am using but it returns a #NA because the cells in the weeks not played yet are blank, players and scores. Is there a better way to do this or a way I can ignore the blank cells until they are entered. Thanks in advance.
=SUM(LOOKUP($A$17,'Week 1'!$C$20:$D$23,'Week 1'!$E$20:$E$23),LOOKUP($A$17,'Week 2'!$C$20:$D$23,'Week 2'!$E$20:$E$23),etc.)
A17 is the players name, 1st range is where the players name would be entered, 2nd range is where the score is entered.
View 9 Replies
View Related
Dec 20, 2012
I have to correct a spreadsheet that takes a bunch of values and creates an average.
=average(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357)*100%
I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.
So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.
I managed to correct that with this
=averageif(N15:N357;"<>#DIV/0!")*100%
It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.
So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try
=averageif(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357;"<>#DIV/0!")*100%
It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.
The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.
View 5 Replies
View Related
Mar 7, 2012
Any way to automatically "rank" cells, ignoring any hidden rows? I don't even really need Excel to sort for me (although I wouldn't be opposed to that), but I'd like the first non-hidden cell in column A to always say "1" and the second one to say "2" and so on.
The back story, if you need it: I have a spreadsheet that tracks sales for 2 separate brands. I've created a macro and linked it to a listbox, so that you can choose which brand you want to view and Excel will hide all rows that aren't pertaining to that brand (I'm absurdly proud of that accomplishment). The only problem is the rankings are hard-coded, so when you hide one brand the rankings no longer make sense.
Every Monday I sort the items by the most recent week's sales and rank them from there. The addition of a second brand is obviously new to my little spreadsheet!
View 1 Replies
View Related
Feb 14, 2007
1. First thing I am trying to do. I have a column of cells that have multiple values, some with text and some with no values at all. I want to be able to display in A1 the most commonly occurring text in cells C1:C15, and be able to display in B1 the number of times that A1 occurs in the same range. Below are the formulas that I am using. There are two problems that I am running into: First, the formula returns a #NA error if any of the cells in the range are left blank. Second, the formula counts the spaces or zeros, so if there are more blanks than the word “amber” then A1 returns “ ” and B1 returns the corresponding number.
A1
=INDEX(C1:C15,(MODE(MATCH(C1:C15,C1:C15,0))))
B1
=COUNTIF(C1:C16,A1)
2. Second thing I am trying to do. In A2 I want to display the second most commonly occurring text in the range, with it’s corresponding count in cell B2, and the third most in A3 and B3, etc
Illustration:
C1 Amber
C2 Red
C3
C4
C5
C6 Red
Desired result:
A1 "Red" B1 "2"
A2 "Amber" B2 "1"
Results with forumla as posted
A1 " " B1 "3"
View 10 Replies
View Related
