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 RepliesI 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
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 RelatedI'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 RelatedI 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 View RelatedIn 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.
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] ........
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.
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.
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.
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.
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
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] ......
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
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?
i need to match 2 columns on one worksheet with 2 columns on another.
One column is alphanumerical (Reference Numbers), the other is company names.
I need to ensure that both the Reference and Company name match from one sheet, with the company and reference from another.
e.g if A1 and B1 on Wks 1 = A1 and B1 on Wks 2 = TRUE, anything else is FALSE.
There is likely to be Reference number and Comapny name duplicates, therefore the trick is to ensure that the number of duplicates match?(I.E ABC Company, Reference number 1234 may appear 5 times on worksheet 1, however if it is only on Worksheet 2 4 times, then this must be flagged).
I want to add these numbered steps onto the macro I already have, which I pasted below.
1. Starting in Row 2 in the worksheet named Report 1 look at contents in Column A then look at the contents in Column C, if the contents in Column C do not match exactly the contents in Column A then copy the contents in the range column C:E from that row till the last row in Column C:E.
2. Now we will have a range that is made up of cells from C:E. I would like to move that range down a row at a time, until the first cell in Column C from the range Column C:E matches exactly the contents in Column A. Then I would like to continue this pattern for every row in the worksheet.
I have two spreadsheets, both contain matchable data. First one with amounts. Next one hs customer name, period and amount columns.
View 3 Replies View RelatedI need to match the first column with the second column, and if is the same value, copy the value in third column to the forth column.
View 1 Replies View RelatedI have two worksheets, sheet 1 and sheet 2. On sheet 1, I have a information on two columns (Column A and B) Column a has information up to row 10 (aaa, bbb, ccc, ddd, ...). On column B, I have 111, 222, 333 . I have same information on Sheet 2. However, sheet two has an additional column (Column C) with 10 rows on information z, y, x, w, v ...
What I need to do is to match column A and B in sheet 1 to column A and B in sheet 2 and if both columns matches on the same row, copy the value in sheet 2, column C and paste it on sheet 1 column C.
Important: Information on sheet 2, column B may be twisted (As they may not appear exactly the same as in sheet 1. Which means 111,222,333 can be 222,333,111 along the column). Therefore, need to match the entire range.
I need creating a formula that matches the values between two separate columns with results from that matching in the third and fourth columns.
Example: Column A contains the first set of values and column B contains the second set of values. The result in column C would be all the values that are in both columns A and B and the result in column D are all the values that are not found in both columns A and B.
Column AColumn BColumn CColumn D
122123123122
123133322133
231221323221
311322231
322323311
323333333
See attached spreadsheet : match_values.xlsx
Alright, so I need data in column A(Last names) and data in column B(First name) to match their respective names on another sheet. When a match is made, it will return column C(Hours worked).
I originally used Vlookup to match last names, something akin to =VLOOKUP(A:A,Week1!A:P,4,FALSE)
But this does not account for employees that may have the same last names. Is there a way to combine two vlookup's? Or is Index a possible solution?
I am trying to 'stagger sort' multiple columns, but am having some difficulties.Ex.
Say A and B are lists of part no and C is data corresponding to column B. I have:
A B C
1 2 .
2 3 ,
4 4 ;
5 7 '
8 9 "
I want:
A B C
1 _ _
2 2 .
_ 3 ,
4 4 ;
5 _ _
_ 7 '
8 _ _
_ 9 "
(the _ are just supposed to be empty place holders, without them I have formatting issues)
Is there a 'quick' way to sort them this way?I am attaching the actual file that I am working with.
My requirement is to match 2 columns of two different sheets. I know about Vlookup for exact matching but here I neeed to do partial matching.
For Example: Sheet1 Company Name= Opera Technologies
Sheet1 Address= 104/Main Street
Sheet2 Company Name= Opera
Output should be like this in different column: Opera, 104/Main Street
I have 2 columns of data. Both should ultimately have identical data,
but both have extranious data I need to separate. These lists will be
300+ long, so by hand is taking forever. Example:
Column A Column B
12345 12344
12346 12345
12347 12346
12348 12347
12349 12349
I need a way to extract the 12344 from column B since it is not in A,
and 12348 from A since it is not in B. They do not have the same number
of entries.
I have a problem in my list cheking. I have two sheets in my attached excel file. I have mordified for my own job security purpose. I need to check the individual cells of "parent" column in "Missing Asset" with "parent" column of "Asset" worksheet. It means, presence of cell text of A2 of "Missing Asst" sheet has to be checked with "A" Column of "Asset" sheet. If A2 text is present in "A" Column of "asset" sheet then "Y" should appear in B2 cell of "Missing Asset" sheet. Else "N". Its just Column text checking wothin two work sheet & then identify the cell which is not match. I guess "VLOOK" can work. Is it not?
View 3 Replies View Relatedi am using this spreadsheet to monitor when equipment is being borrowed in the office and want a quick view reference (columns A-C) based on the data entry in columns &G. Please note that htis is an example as the original file is confidential, if someone can answer my query here i will transfer the formula to my actual workbook. In the actual workbook the data entry and quick view will be on different sheets if this makes a difference.
View 3 Replies View RelatedIn Col A, from A1:A12 I have
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
In column G from A1:A6, I have
6
9
7
11
15
How could I return an 'x' in column C against the A values corresponding with the G values, for example an 'x' in C6, an 'x' in C9, an 'x' in c15' etc? And a blank for example at C8 and C14.
I have the following columns (O & S):
S O
-----
1 3
3 7
6 6
8 4
1 3
2 8
1 6
I want a code (Formula) do the follwoing: Matching number 1 in column S with number 3 in Column O and count how many matches found in the two columns, Which in this case only two.
I have a document where there are hundreds of rows and columns. The rows contain lines of data, while the columns are topics. Each row needs to be traced to an appropriate column. Once traced, I simply check off the appropriate cell with an X. I have to make sure that each row has at least one "X".
I have attached a sample file. look at cell B8. The statement in this row reads "Issue Placard information and registration". Now, I already know what column this statement belongs to (column CT).... but I don't want to scroll over hundreds of columns to that particular column, or have to freeze any panes, to locate that cell and mark it off. I have been doing this manually and it is incredibly tedious and time-consuming. Is there a way where I can locate the appropriate cell and go to it without having to scroll? When I'm scrolling I have to not only scroll, but I have to read the rows and columns again to make sure they indeed match. I want to be able to locate the right cell instantaneously and just enter an X value.