Function For Matching Last 4 Characters To Second Column
Feb 21, 2009
I've been trying to find this on the web but haven't a clue now, I have a database query that I am pulling from an access database and now I am trying to match the last four characters of a referral string to a current location, let me show you what I mean. Example,
A(location string) B(current location) C(last location)
car/kitchen/house kitchen NO
kitchen/garage/car car YES
kitchen/car/garage car NO
I want to write a function in column C that matches the last four characters of the location string (column A) with the current location (column B) , and if they match have a YES in column C, or a conversely a NO.
View 6 Replies
ADVERTISEMENT
Mar 27, 2008
excel filters.
I have excel document over 50 pages and i need to filter lines that begins with certain letters like ADS, SGH, FAQ. I know how to filter one by one but i need all 3 to filter in same time-
How can i make multiple lines to filter in one search?
View 8 Replies
View Related
Aug 5, 2014
I am trying to use some vba match function code to return the column number of the matching date. The date will be stored in a date variable.
Every time I run this code I normally get a match error even though the date is in the worksheet and the variable matches that date.
See below:
[Code] .....
View 5 Replies
View Related
Apr 14, 2014
I am trying to write a function that recalculates given times to points (as used in speedskating).
The times are filled in by a number of people, which each use their own method of dividing minutes from seconds (and 1/100 of a sec).
I found out how to see the difference between times that look like 1.11.11 and 11.11 etc
How to use that same method in an InStr function. Most of the times I get an error message #Value or something that has that format, sometimes all I get is a 0.
View 14 Replies
View Related
Mar 25, 2014
Can I use MATCH to match a subset of characters?
Example....Looking for value in a range that matches cell A1 (which reads 'DOGS')
MATCH(A1,'SEARCH_SHEET'!H4:H2590, FALSE)
This will look for a cell with 'DOGS' in it.
But what if I want a positive match on a cell that has 'DOGS & CATS'
This fails because it doesn't match exactly.
View 9 Replies
View Related
Jan 27, 2014
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
View 6 Replies
View Related
Oct 13, 2009
On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
View 5 Replies
View Related
Apr 10, 2009
I just recently switched to Excel 2007 and am not that familiar with the program.
I have a question that has 3 components.
I have alot of data in Column A that has many blank cells.
I performed several searches and have still not been able to remove them.
This particular method worked if I was selecting a small amount of data
url]
Yet when I use with the entire list I get the error "selection is too large".
What I basically want to do is take the data that I have gathered and transpose it so I can save as a csv file and import in to my contact manager.
View 9 Replies
View Related
Jan 12, 2010
writing a macro script. I want to find matching names on two diffferent worksheets. More specifically I need the ability to match if I apply a abbreviation(s) to worksheet one in order to get matches in worksheet two.
e.g Company = Co. or
Insurance = Ins
Please see my attached excel document for an example.
I have over 27000 lines so doing this manually will take forever!
Ideally I would like this to be a macro script that I can edit to add as many different abbreviations as required. The attached document will make my task a lot clear.
Unfortunately I don't have any starting code as I am not an excel/visual basic programmer, But i hope somebody will be able to help me out here.
The reason I need to do it this way is because I have been given a file from a partner company that uses different abbreviations to the ones we use in my company. I want to see if the companies in the list I have been given already exist in out company database, therefore we need a suitable matching procedure.
View 10 Replies
View Related
Jul 11, 2014
with Match function not matching text in this formula?
Using the below formula cell $L$11 (it contains "abc") will not match the respective cell contents in array E:E even though "abc" is in a cell within column E.
=IFERROR(INDEX('Paste In Data File'!G:G,MATCH($L$11,IF('Paste In Data File'!C:C=B14,'Paste In Data File'!E:E),0)),"")
The cells within column E are populated using a formula such as =IF(MID(A6,3,1)="a",MID(A6,435,9),"")
Column A is the data source and contains a text string.
After much trial and error, it seems the cause preventing the match is; $L$11 contains typed in text "abc" and the above formula for column E is pulling in 9 characters, not just 3.
It seems that if I can modify the entry for the array E:E to just look at the left 3 characters only, it may work.Problem is I do not know how to modify E:E accordingly.
View 2 Replies
View Related
Jun 7, 2014
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
View 2 Replies
View Related
Dec 20, 2013
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
View 9 Replies
View Related
Feb 19, 2009
how to create a macro that:
- In each row, pops up a Warning Dialog Box if the amount of letters in column "J" doesn't match the # defined in column "B"?
- Preferably this would pop up the Dialog Box as soon as the error occurs (so it's always running)
Two Exceptions:
- Does not look at Rows 1 & 2
- Does not look at Rows where column B = 0 or null
View 9 Replies
View Related
Jun 1, 2009
I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.
View 5 Replies
View Related
Jan 8, 2007
I am currently tracking online PPC keyword reports with Excel and need to know if there is a function that will find and match words and phrases and then add the columns that are queried for the matching words/phrases. I think an example is definately in order.
December PPC
Keyword Clicks Cost Revenue
large dogs 45 .18 $12.00
small dogs 35 .25 $15.00
January PPC
Keyword Clicks Cost Revenue
large dogs 12 .14 $8.25
small dogs 18 .18 $5.35
Now using the example above I need a function or maybe even a macro that will scan all "keywords" and find a match for each keyword each month, say large dogs, and then add the clicks, costs, and revenue columns and post them on a particular row or rows.
So when running the function it will find and match "large dogs" for each month, it will then add the clicks for all months with "large dogs" in it and then populate a column or row with the total along with the keyword "large dogs" next to it. Is there a function that will do this or maybe even a macro?
View 10 Replies
View Related
Jan 9, 2013
i have 2 columns of number of cars using various roads, taken in the last 30 days. A macro has processed the data in these ranges and changed the outlier values to "Bad" so the formula ignores them.
However in the columns A and C , there are dates that the counts were taken on.
{=IFERROR(CORREL(IF(B4:B33"Bad";IF(D4:D33"Bad";B4:B33)));IF(B4:B33"Bad";IF(D4:D33"Bad";D4:D33)));"0")}
sometimes these dates aren't the same (no data available on christmas in some areas, boxing day in others etc. Obviously this destroys the correlation, as it puts the days out of synchronisation and the correlation falls apart.
Is there any way i can adapt the formula for it to check the dates are the same before correlating? It'd almost be like a vlookup, find the date, if match, add to calculation, if not then go to the next date. i'm happy for it to only correlate 26 of the 30 days if there are that many conflicts.
example for last 6 days performed on the 28th of december (looking for 6 calendar days up to but not including 28th dec.
22/12/2012 9 22/12/2012 Bad 23/12/2012 10 23/12/2012 22 24/12/2012 3 24/12/2012 21 25/12/2012 7 26/12/2012 18 26/12/2012 8 27/12/2012 15 27/12/2012 15 These 2 cells would be blank and this column would only contain values for 5 days.it could only correlate the 3 days of 23, 24 and 27.
Given that i'm going to be correlating this information with a number of locations, each with different reporting dates, filtering the dates before correlating and deleting unmatched dates is not a great option, but I will consider it if there is no other way.
View 9 Replies
View Related
Aug 25, 2007
Im creating a cash reconciliation application for a BUS operator client that has 6 depots and 1500 drivers.
each driver is uniquely identified by a special number.
long story short, I get 2 workbooks (named Cash1 & Cash2) that I need to have imported by driver number in my main cash rec workbook named Cashrec.
for example; ...
View 9 Replies
View Related
Sep 22, 2009
I have the following info in a table:
name | doc date | doc value
a | 2009/01/01 | IN111
b | 2009/02/04 | IN222
c | 2009/02/05 | IN333
a | 2009/01/05 | CN111
d | 2009/03/01 | IN444
b | 2009/03/01 | CN222
a | 2009/04/01 | IN555
Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this.
Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).
View 9 Replies
View Related
Feb 24, 2014
I have a table as below (you can also refer to the attachment) and need to do the vlookup with EE Code if the left 2 characters of "EE Code" = 91 to 95.
EE Codedeptif left 2 characters of EE code= 91 to 95, vlookup dept code
921600012345678
vlookup arraryA12345623456700
39914123 test785637039914123
38214125 test915580057889056
40964197 test932660038214125
77014155 test913580039914123
345699940964197
945800077014155
953790300123432
915800012345678
045699957889056
585637039914123
View 3 Replies
View Related
Jun 4, 2013
I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.
Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return
View 9 Replies
View Related
Oct 3, 2007
Creating a formula to match two columns (D & H) and if there's a match, I wnat it to produce the value that's in column E, in column J (where the fomula will go).
View 9 Replies
View Related
Oct 20, 2009
I need to know the greatest number of characters contained in any cell in each of the columns in a spreadsheet and display this in row one at the top of each column.
View 9 Replies
View Related
Feb 6, 2010
I have a column which cells are populated with codes like
BGW
3UG
WWU
1BR
UUBRG
WUBRG
7UUGGRRR
and so on. As you could see, we'll always have a number from 1 to 20 (or no number at all) followed by those letters (which can be only W,U,B,R or G). I'd like to know if there is a function to count the # of times a particular character occurs in a colum. In the above example, the "U" character appears 8 times. This would be nice to automate part of the filling process.
Just a little difference: since numbers always come before the letters, I need to treat a number sequence as a single value: 12GWW should not be seen as 1 then 2, but "twelve", like it was a single character. So, if I have this column...
URR
12WBBB
6UBG
12R
12WG
...then "12" appears 3 times. Nevertheless, B appears four times (3 on line 2 and 1 on line 3.)
View 12 Replies
View Related
Oct 25, 2007
I am trying to reformat the following data: 10-10-14-1W5 needs to look like this 100101001401W500. The full description is actually 100/10-10-014-01W500 the desired result is without the slashes and dashes. I have tried to add a custom cell format of 00-00-000-00L000. but it will not apply to the existing data.
View 5 Replies
View Related
Mar 4, 2009
I have a column of data, (10,000 entries), a list of file names basically, e.g:
Mortality Project Executive Summary.pdf
RPP - AA rate and swaps.xls
Commodities.PPT presentation.ppt
Meeting Preparation 20090302.docx
Anywho the point is I want to remove the file extensions (and of course the "."s just before), but some obviously have 4 character extensions, some 3, some 2 etc... Some documents also have "."s in the file name that I do not want to remove, basically just working from the right keep removing until the first "." is removed.
So the final list wants to be:
Mortality Project Executive Summary
RPP - AA rate and swaps
Commodities.PPT presentation
Meeting Preparation 20090302
I know I should VBA it, but I want it all in the one spreadsheet and that's a bit above me. The document has about 30 other columns.
View 3 Replies
View Related
Sep 9, 2013
I have a long list of items in one column, with parameters that are all split by an underscore.
However, in some instances, some of the text that is separated by an underscore needs to be joined together.
So for instance I'll have;
This_is_a_very_good_thing
This_is_a_perfect_thing
Where I want the result to be that "good_thing" becomes "good-thing" and "perfect_thing" becomes "perfect-thing".
How can I get this done with a Macro, instead of constantly doing a Vlookup to change this instances.
View 3 Replies
View Related
Mar 6, 2007
I have columns and rows that are populated with one of three letters: P, S, & T. At the bottom of the column and at the end of the row, i'd like to assign a numeric score to the entire column/row that is based on converting the characters to a numeric eqivelant. P = 3, S = 2 and T = 1. I'm guess a case statement would be used, but not too sure here. So, I'd need the code behind the worksheet as well as the formula(s) used for the score. I've attached an example.
View 3 Replies
View Related
Dec 15, 2013
I have 2 lists. One list consists of the names of agencies as they would appear in our company's data base. The other list is the official business names. I would like match our company list with the official business name list by matching at least 2 words from the name with a name that shows up in column C (Official agency name).
If possible, I would like to ignore preposition and article words since they are used often in agency names.
I am looking for a formula to do this
View 3 Replies
View Related
Jul 10, 2009
I have a problem with excel matching fields. I have a list of cities in column A, a list of state in column B, and then on worksheet 2 i have another list of cities in column A and states in column B. I then have dates in column C on the second worksheet.
The problem is that the list on worksheet 2 do not repeat the names of cities and states but on worksheet one there can be repeats.
I wanted to know if there was a command so that if Column A and Column B on worksheet 1 matched Column A and Column B on worksheet 2 then excel will copy the date in column C on worksheet 2 and paste it onto column C on worksheet 1.
View 6 Replies
View Related
Dec 31, 2013
In column B I only want to show the results from Column A if the character count equals 3. In Column C I only want to show the results from Column A if the character count is greater than 3
Example:
Column A
ast
john
julie
tyw
nhy
frank
View 5 Replies
View Related
