First as said in title i have no idea what these kind of tables is called in English and i didnt know how to formulate me when it came to the title (so hint me with a "right" name for this kind of table)
To the point i have alot of these cross reference tables. What i mean is table that have a horizontal titelbar (row) and a vertical titlebar (column) and in the table you have the diffrent stats or whatever that can be within it. Im having a really hard time expaining this so im just uploading a excel file with a little explanation of what im after.
I have two lists of reference numbers; I need to see if any of the references appear in the other list. I don't want them to be removed, maybe highlighted?
I have made them into one list and used the duplicate conditional formatting but some references appear twice in one list and there is a lot of data so this is not good enough.
formulas i cannot get my head round at all i need e14 to work out how many times cell e1 appears in column h and in column b. so if yes is in e1. it then needs to look down column b and cross refernce it with column h and return the number of yes that appear in column b AND the same row in column h.
I am involved with regional sales and have developed a spreadsheet to track various statistics and information regarding the various cities and clients.
On Sheet1 I have Column A for the city names. Columns B through R are various statistical information (all numerical) associated with that city.
I would like to use Sheet2 to quickly draw that information into a generic "printable" spreadsheet such that I type a city name into Sheet2 A1 and the rest of the cells automatically draw the information for that city into their respective cells.
Looking through other threads I thought that maybe VLOOKUP would be the ticket, but it's only returning a #VALUE! error.
I am having to figure out a particularly painful excel formula. What i need is a formula that will count the number of times an issue label in text shows up, but it has to match a certain time.
we can get the excel sheet to round the time to the nearest thirty minutes, so if a call comes in at 3:12pm it rounds it to 3pm. We label the calls that come in with a issue description also like "dvr issue." So i need a formula that will allow me to count the number of instances where if it says 3pm AND dvr issue it will only count it and give me a total in a specified cell. Is that possible?
I have a spreadsheet with Company A's part numbers, then the number for the same exact part from their rival company or competitor. When searching on our website I need for the customer to be able to find the part even if we are out of stock of Company A's part, we can let them know we have company B's part and it is the exact same part. So I am cross referencing the numbers.
In my spreadsheet I have all of company A's parts in column A and their competitor parts in column B. In another column I have cross reference #'s I did with the concatenate function. Where it gets tricky is one part can have 50 or more numbers associated with it. So far, I have been doing the concatenate for each one. I haven't gotten very far as you can imagine. Is there a way I can have it check a column, then when it finds the same exact numbers have it put the numbers from column B all in the same row seperated by a space(my delimiter)? See image at link. I couldn't figure out how to post the spreadsheet. Maybe I am too new to attach files.
[url]
so in the example above, cell E11 has company A's part number, it's own number, then the number above it and the numbers below it. It has every number associated with 74679142.
I am trying to match/cross reference (check by ZIP CODE) two extremely large databases/spreadsheets in MS EXCEL. The vast majority of matches will turn up negative (no match) and only like <5% of all the rows/fields in my 31000+ excel sheet will actually match by zip to the records contained in another sheet (actually this second sheet is a large Seibel Sales database that I exported to Excel for easier access/manipulation) So it would be very tedious and a time waster to manually match the records by hand. My question would be, is there an automated way in Excel (or using any number of Excel addons such as ASAP Utilities etc) that would let the computer do the work for me?
Basically I have two large records, one that I exported from Siebel online to Excel, and the second one is the original work Excel database that I am to work on (ie find matches by zip code and mark them as matched)
Is there a quick way to do this? The power of the computer should really be put to use, but I just don't know /cant figure out how to go about this.
here is the screenshots and explainations if you don't know what I am talking about: [url]
Just to clarify (I'm not sure I explained it very good)
Imagine two sheets
Sheet #1 <-(Seibel converted to xls) Sheet #2 (xls sheet to be worked on) 12345 --------------------------------54986 (mark as NO MATCH) 84596 --------------------------------25746 (mark as NO MATCH) 24578 --------------------------------12345 (mark as MATCH!) 96328 --------------------------------15789 (mark as NO MATCH) 45897 --------------------------------96328 (mark as MATCH!)
The problem is Sheet #2 is not only five rows, it is like 30000+ rows, and I will have to do another 30000+ rows soon, so 60000+ to do by hand is very slow indeed.
Also, >95% of all the rows in Sheet #2 will be a NO MATCH with no matching zip codes to ANY of the zip codes of Sheet #1. So less than 5% will actually be a match, but the thing is I have to check ALL of them to actually know which ones match and which don't.
IS there a utility / macro or function in Excel that can quickly let me do this the automated way?
Basically I have two files. File A) Consists of a billing spreadsheet of approximately 2500 orders. File B) Consists of a Customer spreadsheet with about 2400 customers. What I want to know is there a fast way to confirm based on customer last name and customer number that for each customer in file b that there is a billing entry in file a. I've gone the V-look up way which is effective but time consuming also. File A & B are going to get progressively larger and larger; thereby making the v- lookup not a very timewise solution.
I am trying to create a cross-reference database in excel. I am not sure if I am using the correct terminology, so here is what I need to do:
I have three columns that contain text cells, each with different numbers of rows. Now imagine that cell A1 is related to cell B3 and also to cell C5. There are numerous different combinations similar to this. Is there a way to show this relationship between cells in excel. If so, how?
I need to "report" somewhere and somehow within the spreasheet hopefully by means of some formulas the following data:
1. for column "C" the maximum value within first group of consecutive numbers that are exceeding 180: in this example equal to 201
2. for column "C" the total number of values within first group of consecutive numbers that are exceeding 180: in this example equal to 4
3. for column "B" the first number corresponding to the first group of consecutive numbers that are exceeding 180: in this example equal to 1
4. for column "A" the corresponding number to the maximum value within first group of consecutive numbers in column "C" that are exceeding 180: in this example equal to 5
and most of all I would like to generalise the problem so that to "report" the same data for all cases that are appearing in the above example;
I am working with two sheets, Sheet one has 1 column of information with 12 columns to the right as categories. I want to be able to put an 'X' in any one of the 12 column rows, (category) and have a function that will copy/pastes the information from the main column (column #1, row=any) to the second sheet. I have tried True-False function, argument function, Vlook etc., I still cannot get it to work.
I'm using VLOOKUP to create a Cross Reference tool. CR is my "anchor" page that I'm attempting to tie the remaining worksheets WIT,TEC,COP back to the CR worksheet. I'm able to get VLOOKUP to work on the CR worksheet in columns H & I but unable to get the VLOOKUP to work in column J. The VLOOKUP function is entered but it does not return a value that I know exists in worksheet COP.
Is there a way to Cross-Reference two sheets to find the duplicates and highlight them or another way of making it defined? I do not want them deleted, just need to be able to tell who the duplicates are.
Sheet 1 data has
A1 - Email Address B1 - Contact Name
Sheet 2 data has
A1 - Email Address B1 - Contact Name
I want to be able to find the duplicated email address' in sheet 1. And both spreadsheets have over 10,000 email address'. Is this possible?
I have attached a sample of the issue. The formula is found in cell B49. If I enter a gravity of 2.76 or higher in cell B47 I get #N/A in cell B49. Why will it not read any farther over than 2.75. I have been starring at this for awhile now. I might look pretty dumb after someone else looks at it.
I have a sheet (sheet 1) of employee information for multiple buildings as follows:
Employee Name, Building, Pay Grade, Full Time Equivalent (of full time hours worked)
Anne, Centre A, Anne, Grade 2, 1.0 Bob, Centre E, Bob, Grade 3, 0.50 Carl, Centre H, Carl, Grade 2, 0.60 Dan, Centre B, Dan, Grade 1, 1.0 Emma, Centre A, Emma, Grade 3, 1.0 Fash, Centre A, Fash, Grade 2, 0.40 Gemma, Centre C, Gemma, Grade 2, 1.0 Hanna, Centre B, Hanna, Grade 1, 1.0 etc, etc, etc
From another sheet (sheet 2), I wish to populate the total of "Full Time Equililents" at each grade in each centre, eg:
CENTRE, GRADE 1, GRADE 2, GRADE 3 Centre A, 0 1.4 1.0 Centre B, 2.0 0 0 etc,etc,etc
I am trying to drop (paste) in new data in a range in sheet 2 and cross reference a column with a table in sheet three and display all rows of matching instances in sheet 1. Example:
Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.
Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.
I want to make a crosstable (7 columns X 6rows) but the thing is that I only have the total number for each row and for each column, how do I do to get the whole numbers of the crosstable?
I have a spreadsheet that shows a large number of folks we had working in a particular division from Jan. 1st until now, 1800 +. Some of the folks are on the sheet twice due to having more than one role. The sheet lists last name, first name, skill description, pay rate, company they worked at...etc. Most of the folks on the list are not currently working but some are. I have another spread sheet that lists the folks that ARE currently working. I'm trying to see if there is a way to compare the two sheets, via a formula, that will be able to identify when the first name, last name, company name, and skill description are the same and then have those identified deleted from the first sheet.
I want to reference a table (not a pivot table) from another Excel file. I want it to update automatically if the original table is changed in any way.
- a list of data - a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
I've been using the vlookup and hlookup functions for a while, but am not an expert and I've come across this scenario. with one of my larger sheets (a few hundred lines and a dozen or so columns - I know, too much info for a spreadsheet but coworkers are used to them) if there was a way to combine the hlookup and vlookup functions to find a specific value if I give it the vertical axis value as well as the horizontal axis value. Is it possible to combine these two or perhaps another way to find the value if the column and row are specified? Just to clarify, there is no repetition of rows or columns.
The spread sheet I work with will not post info on the second sheet automatically. So I'm in the process of trying to figure that out. The second page is basically giving a percentage of parts the person did compared to what another company would pay that person per hour to do at 7.42 an hour. I need the second page to take all the info on the first page and figure out the %. What it does to get it is, adds the numbers and divides by the total to get average and then compares it to 7.42 and hour.
Also, if i could get the Name boxes to be auto filled with the file name? that possible? IE, John smith is the name of the file and it would auto fill that into all the name boxes on the sheets. And could i get the date to auto fill it self from the first date box? I included two files, one is a blank sheet and the other is a sample of the data entered and what i want it to look like.
I know how to turn a column reference in a structured data table into an absolute reference:
=Table1[A] becomes =Table1[[A]:[A]]
However, how to make a cell reference, like this one, absolute to that it still locks on this row, column A when I drag it across. I don't want to use copy & paste, as I have other cell references that i need to leave dynamic.
=Table1[[#This Row],[A]]
I've seen that @ can lock rows, but I don't seem to be able to lock an individual cell.
I have been searching for this but have been unable to find an answer. Lets assume I create 2 tables. In the second table, I want to reference a specific cell from the first table in a formula (so that when I sort the column with that specific cell in the first table, the reference in the 2nd table will still work correctly and not change). How would I go about doing this?
I'm trying to take data from multiple worksheets and copy them into a summary sheet in the same workbook, and then make a pivot table from the summary sheet.
I got the first part done, but I can't figure out how to make the pivot table to incorporate new data in the summary sheet as new worksheets/data is added.
I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.
However I am getting the #VALUE error when trying this.
Simplified Example: I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE) I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.
And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE) This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.
