Matching Names In Different Formats
Sep 21, 2005
I have 2 reports that I want to merge which are exported from different applications. The problem is that the names appear in a different format and I can't work out how to match them up. The fact that not everyone appears on every report I have solved by using lookup as in the attached workbook. What I need to do is use a formula that will recognise the two different name formats and match them up. i.e. Campion, Ben P. and Ben Campion. See attachment.
View 6 Replies
ADVERTISEMENT
Jan 28, 2010
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
View 9 Replies
View Related
Jan 14, 2013
I am constantly given the task of comparing to list of names that come from different sources. One source(SourceA) provides the names in a two Cols as first name and last name. Luckily Source A usually just has first and last name list but sometimes they have a third name or middle initial included and it could be in the first Col or the second no real pattern. The second source(SourceB) provides their list as a single Col and it has every given name for the person including suffixes and prefixes. A couple of examples of Source B names would be-"John Henry David" or "Dr. Billy Bob Adams Barone Beard IV".
I have spent countless hours and days probably weeks on some list as there are anywhere between 20-50,000 names per list I am sent. I am hoping there is a better solution to compare these names. As of right now the only thing I can do right now is manually trim the data from SourceB down to a format like "John David" and "Billy Beard" and then combining the Cols from Source A into the same format and then comparing them.
View 3 Replies
View Related
Jan 26, 2008
I have a names in column A starting with row 7 as follows:
Walden, Douglas E
Haden, Michael
Wilson, Matt David
I need help with coming up with 3 formulas:
(1) That returns the First, Middle Initial, & Last Name in column P of the same row:
Cell P7 Douglas E Walden
Cell P8 Michael Haden
Cell P9 Matt David Wilson
(2) That returns the First & Middle Initial in colum Q of the same row:
Cell Q7 Douglas E
Cell Q8 Michael
Cell Q9 Matt D
However, I am so new at this, the simpler the formula the better because I want to try & understand it, not just learn it.
View 11 Replies
View Related
Aug 18, 2014
I have two columns listing about 400 and 500 names respectively.
First Column has names in alphabetical order and the second column is random.
One column has names in this format: Last name, First name and the other column lists the names as first name and then last name without the comma .
I am trying to see if the list of names in one column exists in the other.
Is there a way to see this without changing the format of the names in each column? If so do I have to erase the comma?
Name Lists (1).xlsx
View 3 Replies
View Related
May 1, 2009
I'm trying to find a way to check if the first and last name that are the content of cell A are anywhere in cell B and beyond. I've included an example: http://tinypic.com/view.php?pic=28cpkr9&s=5. In the example A1 must match B1, C1 and C2.
View 5 Replies
View Related
Mar 24, 2008
I have a list of names in a column and another list of names in another column. The columns are named patient and user. Is there a way to right a formula that would highlight a name match when a name matched in the two columns in the same row?
View 9 Replies
View Related
Sep 1, 2009
In Cell A1 I have a name (John Doe). In cell A2 I want the formula which will be a number total. In Cells A4:A20 I have a list of names with John being in several of them. In Cells J4:J20 I have numbers. There is data in between the name and numbers that I don't need.
So, I want A2 to search A4:A20 and everytime it sees John Doe to add the numbers that are 9 cells over (J4:J20). This is basically to show how many mistakes John has made (total). I tried =Sumproduct((A4:A20=A1), (J4:J20,1,0)). I know I am missing something or not on the right track.
View 2 Replies
View Related
Jul 17, 2014
I have a worksheet that has a compilation of reports, each of which has multiple columns that aren't necessarily organized in an Excel-friendly way. However, the first column contains a list of "name" headings (titled "Name") and the second adjacent column always lists the actual name right beside it. Similarly, each person has a value heading in the first column (titled "The Value") and the actual value in the second column. What I would like to be able to do is have Excel search for the Name heading, paste the value in the adjacent column (the actual name) in a cell in a new worksheet, then search for "The Value" and do the same thing in the column adjacent to the one where the actual name was copied. The catch is I need Excel to do this and then search again for "Name", skip it, and start again with the first part of the procedure until all names and their associated values are matched. how to do something like this in VBA?
View 6 Replies
View Related
Sep 3, 2012
I'm working on a project that has 2 sheets. the 2nd sheet has the raw data and the first sheet will be the sorted data.
To deliver the raw data on sheet 2 to sheet 1, I need the different names on sheet 1 to match to its corrosponding name on sheet 2 then deliver the value in another cell. I don't need the zero values but on some occosions the zeros and numerical values will be switched so i will need a formula that will deliver the numerical value to sheet 1 after it checks which cell contains the numerical value between columns B and C. there will always be a zero and a numerical value in either column B or C for each.
It will look like this. (assume 3 rows A, B and C and each value and name is in its own cell).
Sheet 1
Name
$
Interest
Cash
Dividends
[Code] ........
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
Sep 25, 2011
I have a list that looks sort of like this:
Last Name | First Name | Date | Service Day
Smith...........John....................Monday
Jones............Alex...................Thursday
etc..
This list goes on down for like 50 cells. Anyway, I'd like to make a new list based on Service Day so that I can organize the names under the service day so it should look something like this:
Monday | Tuesday | Wednesday | Thursday | etc..
Smith, John..............................Jones, Alex
etc...
I tried using the vlookup function but then it confused me when i also needed to match the names with their respective service days.
View 7 Replies
View Related
Apr 5, 2007
I have an excel sheet which displays the data in the following format:
(See image attachment). This is just an example of the way the master list is formatted. The master list has over 18,000 lines. Basically, there's a project name, resource name, and number of hours worked for the project within that work week. I've been trying to figure out a way to create a formula or macro to search all these names and add up the hours of the duplicates.
I need excel to search this list of names and total up all hours for each matching name and place each total value on another sheet. For example, Ken Craver has 8 occurances within the week of 1/21/2007 (from project 1 and 25) and his total hours = 31. So the value 31 should be calculated because all the hours associated with Ken Craver add up to 31. Then excel should scan another sheet which has a list of resource names and once it has found the matching name on the other sheet, it will place the value 31 in the column next to the name.
View 4 Replies
View Related
Jan 8, 2012
I'm trying to match stock portfolios of mutual funds with stock indexes to find out how much the portfolios deviates from the indexes (active share).
The deviation is simply calculated as the percentage of the portfolio holdings not included in the index.
The data I have is 10 years of monthly portfolio weightings for 30 mutual funds and 8 indexes, organized with company names in column A and the percentage weight for each company in each month in the adjacent rows (see simplified screenshot). I want to match all companies with all indexes.
Twofold:
1)The mutual fund data comes from one source and index data from another. This means company names are not spelled exactly the same.
2)Within the mutual fund datasets, a company might be listed more than once with different names. The different names refer to different periods in time, so what should be matched with the index is the sum of these columns.
The specific task that I need to accomplish is to match company name in the portfolio with the index and then return the absolute difference between weight in the portfolio and the weight in the index for each month. (see screenshot).
Hence the result I'm looking for is a matrix of company names (of each portfolio) in col A and the absolute difference between the portfolio and the index weight in the adjacent row.
The best solution I've come up with is to device a list of all the portfolio company names, including "doubles", and then for each index, add these company names and copy the row weightings. This I'm not particularly happy with because index weightings would no longer sum to 100 and it would be difficult to check for errors. Also, it would still take a lot of time.
View 6 Replies
View Related
Nov 26, 2012
I'm trying to do a countif that matches 2 names and returns the amount of times the 2 name matches is in the list. Ihave first names in column a and the surnames in column B.
And there are lots like so
John
Greenwood
Bill
Griffiths
Melanie
Griffiths
View 3 Replies
View Related
Oct 21, 2013
Identical Data exists on Sheets1 and 2 (A3:V50)
A3:A50 = human names
B3:V50 = numeric values
I'm trying to VBA code:
If human names on Sheet2(A3:A5) match human names on Sheet1(A3:A5)
Move Sheet2 matching name row(s) to the bottom of the data range on Sheet2.
Sheet1
Sam 4 4 4 4 4 4 4 4 4 36 4 4 4 4 4 4 4 4 4 36 72
Bill 4 4 4 5 4 4 4 5 4 38 5 5 4 4 4 4 4 4 4 38 76
Bob 5 5 5 4 4 4 5 5 5 42 4 4 4 4 4 4 4 4 4 36 78
[Code] ......
View 3 Replies
View Related
May 21, 2014
How do I create a macro to sort rows into groups, add break rows in between & insert a product sourced from another excel workbook onto the break line?
I receive a spreadsheet each day with a series of requests, most of the information is the addresses & names etc with the important data in columns K,M & O. Data in each of these columns is a code that refers to a product and information sheet to be sent out to the person requesting it. They may want just one in which case data will only be in column K, if they want two there will also be data in M and if they require three there will be data in column O as well. The bit I do know is how to sort the important data into groups of the same by the country (column I) and the product sku's.
Whilst I can do the sort as a recorded macro I really need it to be part of a larger macro that does the following bit I can't get my head around. I need to insert a break row above each group then by cross referencing the data in the product sku columns to a separate workbook insert the appropriate item / letter combination onto the break line row, telling the volunteer which items to send out. This is a v look up?
I have attached an example of the files we work with, the first tab is with the data simply sorted and the second is what I need it to look like. I normally receive it as a CSV file, there are around 8000 possible combinations of product and instructions and the files are normally around 4000 records long. At present we insert the break lines and copy/paste the information manually, seriously slowing down the process.
Orders Example EUR RM.xlsx
View 12 Replies
View Related
May 21, 2013
I need a macro to create a hyperlink i.e....... I have an excel sheet with numbers in column A.....I have files in a folder that matches the numbers in column A....
How would I create a macro to create a hyperlink from the numbers in column A to the "matching" file names in a folder?
View 3 Replies
View Related
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
Nov 19, 2009
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
View 12 Replies
View Related
Feb 27, 2014
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1:
50025
50026
50086
Table 2: Considering that name, age and location is each in separate cell
50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7
And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:
50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5
View 2 Replies
View Related
Feb 20, 2014
I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
View 1 Replies
View Related
Jan 13, 2014
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
View 9 Replies
View Related
May 7, 2012
Can I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies
View Related
Oct 15, 2013
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
View 2 Replies
View Related
Jan 4, 2013
I have a worksheet with many tables that I use in formulas.
I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.
Also, automatic copy of formulas/formatting is great.
But, what I really like about tables is the ability to use the naming conventions in formulas.
Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.
Example:
Code:
=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"
View 4 Replies
View Related
May 1, 2008
Two part question:
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
View 9 Replies
View Related
Feb 25, 2011
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
View 3 Replies
View Related
Aug 26, 2008
I am using Excel 2003 and Windows XP.
I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:
Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.
What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.
I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was
=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)
So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.
My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.
Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?
View 9 Replies
View Related
Sep 1, 2009
I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?
View 2 Replies
View Related
