Matching Names With Numbers (total)
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
ADVERTISEMENT
Apr 17, 2009
If a red number to the right shows up anywhere on the left black numbers then I want a total in column G. I want it to automatically total the matching numbers. How can I do this?
View 3 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
Nov 16, 2011
I have one workbook in that 6 sheets have.
Here I want match the total one sheet to another but each sheet there is no fixed ranges so that I have confused.
How to find out Each sheet total and match each other.
View 3 Replies
View Related
Mar 12, 2013
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
View 1 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 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
View Related
Sep 11, 2008
I want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 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
Oct 8, 2013
What function should I use to find the total length of the 3 extracted names (last + first & middle title?) I need one single expression for the entire column.
A
B
C
D
E
Full Name (Last, First & Middle plusTitle - the latter may be separated by either an ! or $ symbol).
Title (extracted)
First and the middle name if the latter exists. (extracted)
Last Name (extracted)
The total length of the 3 extracted names (last + first & middle + title) e.g. 25
6
Brouillet,Amy Anne!Miss
Miss
Amy Anne
Brouillet
[Code] .......
View 6 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 5, 2009
I have certain cells in column A2:A22 that have names of people. I want a formula in Cell A23 that gives me a total of how many names are in that range. I know this is simple, but how do I put my criteria that if a cell is not blank to count it?
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
Jan 2, 2010
which formula achieves the following:
i have a simple table design with the dates running across the row, below each row i write the name of the person on shift. in the 3rd row i mark the time they are late for work (if any).
below that in a seperate table i have a list of my staff next to that i would like there to be a running total of their lateness. I need a formular that will look for their names and then total any lateness that i have inserted. I have include a basic spreedsheet of what i want to achieve, the column in RED is where i wan the totals.
View 2 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
Apr 16, 2007
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies
View Related
Mar 27, 2014
formula to take a value from one sheet and add to it in Excel 2010? I'm naming the sheets but not necessarily numerically. I have done it by manually entering the sheet name but would like it to figure out the sheet name automatically based on where the sheet is located in the workbook. That way I can copy the current sheet, rename it and still have it update properly with a running total. Here is what I have now: =D7+'011514'!E7
View 2 Replies
View Related
Nov 29, 2012
I am trying to find a function which will do the trial and error bit for me. I have for example 5 numbers an want to fit the answer to the puzzle.
For instance
3 5 X
2 3 Y
1 2 4
Is there a way to find "X"? Then as a result "Y". I can't just go back up the equation as it is not hat simple. Is there a way excel will try an find what fits?
View 1 Replies
View Related
Nov 9, 2007
I have 10 separate columns with an unlimited (but right now 354 rows) and I would like to search for two matching numbers going row by row, giving me the answer in one field that would say how many rows the two matching numbers are in, is there a way?
View 9 Replies
View Related
Jan 14, 2010
I have a very large file with two columns with about 40,000 rows, is there an excel formula to match the same numbers in column A to the same numbers in column B and all the other non matching numbers to be pushed to the bottom of the columns.
View 9 Replies
View Related
Feb 1, 2010
i had a subtotal sheet with more than 5000 rows of data.i applied subtotals for my data.i just want to serial numbers for each of the subtotal.what is the easy way for numbering my rows with sub total.
My subtotals are in column A.i inserted a column before it and want to show the row numbers which contain the subtotals.
View 10 Replies
View Related
Oct 4, 2006
Keeping track of racing point scores and I am trying to figure out a formula that will allow me to take the top 6 out of 8 values and total them. We compete in 8 races but the total points standings at the end of the season takes the best 6 out of 8.
For example
Racer 1 100 200 150 90 300 250 210 95
I am looking for a formula that will give me a total of the HIGHEST 6 scores. I tried the =max but that will only allow you to get the HIGHEST value.
View 3 Replies
View Related
Aug 13, 2007
I’m creating a spreadsheet (attached) that needs to divide up aisles in a warehouse into sections, so that each section has an equal (or as close as possible) amount of work. The “To, From” columns are aisle ranges and the “Moves” column is the amount of work in that range. I need a macro/formula to add up numbers under “Moves”, but only up to the point where the sum is as close as possible to the number in K2 (variable). It also needs to look at the number under “sections required” (I2) (variable) and then split the aisle ranges into that many sections.
View 2 Replies
View Related
