I have a large database of service invoices. Since our invoice can carry multiple lines, the table in question might have multiple entries for the same invoice number. I'm trying to generate a performance metric dashboard for the service group by employee. Again, the employee can show up multiple times per invoice if he performs different work for each invoice.
I'd also like to condense the list down so there aren't any blank rows between the rows with data.
Ultimately, I need to find each individual invoice that each employee worked on and generate a list from which I can then generate an SPC chart. I'm willing to do this in a couple of stages if necessary. Primarily, I want to avoid using VB script if possible.
So, cherry pick service invoice numbers from a list when matched to an employee's name (VLOOKUP), consolidate multiple entries when that employee's name matches multiple entries of the same service invoice number, and condense the list (preferably as it's built) so there are no blank rows.
I need to consolidate these two lists of data into one list.
First set is just a basic list of individuals with their data.
Second set is multiple entries for those same individuals. Each entry shows a subscription to a programme. The final single sheet should have one row per individual which shows all of the programmes they are subscribed to.
See attached, example sheet.
The real list is 3000 IDs, so need some kind of formula to do this.
While making my first foray into defining external connections (to an Oracle database) I ended up with an "Existing Connection" that reports "File could not be opened. It is either damaged or the file format is not valid. Try opening the application that created it and saving to a new file." Followed by "Unable to open or read this connection file. Either the file has been damaged or the file format is not valid."
Ok, fine. So how do I delete it? There seems to be no option for that in the "Existing Connections" dialog. A search for a *.odc or *.udcx file doesn't turn up anything with the name of this connection. A search of the registry doesn't turn up anything.
I am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
I have a spreadsheet that needs to have a validation drop down list in three cells in every row (D, E, F). There are three items in the list, say, "One", "Two" and "Three".
My only problem is that there can only be one entry of "One" in each row.
Is it possible to create a formula using a Data Validation List where it will add a new entry to the existing entries in a cell? For example: I have a data validation list with the names of Pete, Chris, Bruce, Carly, Megan, and Becky. With the normal data validation set I can select one entry and it will be output into the designated cell. If I select Bruce then Bruce with be placed in the cell. Is it possible to set it up so that each time I click on data validation it will add an entry to the already existing entry? E.g. I have already selected Bruce then I decide to add Becky, I want the output cell to show both Bruce and Becky.
I know I can do this with a formula that will place all the results from different cells into a single cell, but I would then have to create multiple data validation entries of which I would rather avoid if possible. Not to mention I don't remember how to do this formula anyway......
I am trying to do 2 things involving data validation. 1) Count the number of entries in a data validation dropdown list and display a result. 2) Use an If statement to pull the formatting from a cell into another cell.
An example would be if Cell A1="A", then show the drop down list associated with cell or sheet "!ryanB2". Alternatively, I would like to also display the number of entries in the drop down list.
So i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.
I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.
I'm using Xl 07 and need to combine two sheets of contact info and have them sorted alphabetically.
Each contact entry takes the follwing form(comma's denote new columns)
55555, Name , John, Smith, Tel: , 555-555-5555 ID , Company, XXX , , Email, xxxxxx
Each sheet has a list of 200 or so entries about 400 rows long. The entries on each sheet are sorted in alphabetical order. the names also alternate alphabetically from sheet to sheet. So when i look at the entries on my final sheet, the 1st will be from sheet1 the second from sheet2 the third from sheet1 and the fourth from sheet2 etc.
I'm not sure whether I should build a new list by pulling alternating entries from each sheet or just copy one list below the other and then try to sort it somehow.
I have macros that format them accordingly, run formulas, cleanup, etc. My problem is that my View Macro screen to run them has gotten very cluttered and confusing. I've tried looking for some sort of add-in that might make for a better way to organize them that's more visually appealing, but can't find anything. I also tried searching this forum as well. Am I just being picky? How do you guys that have 100's of macros organize them all?
I have an Excel spreadsheet that contains information for a Word document. It uses an elaborate macro to "substitute" unique codes in a starter document. At completion of the process, it goes through and delete all unused codes
The problem is that the codes are preceded by a "bullet" symbol.
How can I remove that orphaned bullet symbol. If the code is ". CtlCode10" (where the . is a bullet), then ALL need to be removed
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
I have two lists of product data, one for buyers and one for sellers (these are listed as A, B, C). The product names are not exactly the same (Eg Playstation and playstation three should be matched), I would have thought using the FuzzyLogic add in to match these would be the way forward! I need to rank the sellers by how many of their items appear on the buyers list
I have a list of email aliases for my company, approx 10,000 entries, mapping the alias to an email address. Two columns, one the alias firstname.surname, second column being the full email address. I have a second list, a subset of these aliases, approx 1200 of them, that are users of a particular web app. All i have in this list though, is the firstname.surname bit.
What I need to do is compare the lists, and where the aliases match I need to pull out the corresponding full email address from the long list, so I have 1200 aliases in the shorter list, with the full email address mapped to it. Is this possible in excel? i've tried all sorts of LOOKUP functions, and MATCH functions, but none seem to work. MATCH will give me the corresponding row number in the long list, but then it seems to offset itself every row it goes down, so the values are 1 out for every row. First row is good, second row -1, third row -3, fourth row -4 etc.
I don't want to have to manually go through 1200 entries and match them against a 10,000-entry list. Hopefully there's a quick way in excel to filter the long list down to the entries specified in the short list?
I have a dynamic list of names from B2:B500. I want to write a macro that finds all the unique entries from that list and pastes it to AD3:AD501 everytime the macro is run. What would be the code for this procedure.
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows
Example (I have the below in a single Cell as column headers) NCM Server Mgmt VLAN Site ID
Next Line down is the data (Each row in a single cell) Enabled 10.10.10.0 50 TEST SITE 1 Enabled 10.10.20.0 50 TEST SITE 2 Disabled 10.10.30.0 50 TEST SITE 3
How I could achieve this as I have a number of projects where this would become useful
I know you can use delimiters but with spaces between the values I just can't fathom a way forward.
I have column A and it has 1000 rows, every row has a number in it, from 5000 to 5200, meaning that some numbers are presented multiple times in column A.
I need to lose repetitions, so every number is in the the table only one time and then I need to convert this one long column into, for example, 9 columns, so there's no wasting of space and have only one column in every page, if printed out.
I have a list of U.S. cities and their crime numbers. The list contains roughly 8700 records. What I need to do is pull the records for 10 specific cities from that list. Exactly which cities are singled out may change from year to year. And this is something I'll need to do every year when the crime stats are released by the FBI.
I have the below table of data, in Column A there is a list of entries and there may be duplicates in them, what I want to do in column B is be able to list all the unique entries in there and basically remove any duplicates.
What I am having trouble with is after making a data validation list in a column, I need to count each separate list entry and display it in a "totals" column. The drop down list has 4 entries yes, no, blank, and pending. The formula must count which value has been selected from the list and return it to a cell same as the COUNTA. I.E. 100 rows in the SS 50 are yes, 25 are no, 20 are pending, and 5 are blank.
I have a list of names in column A with monthly budget figs next to them in columns B,C,D etc. At the bottom of the list there is a total spend summary on line 7. This spreadsheet is then pasted with links to a separate worksheet (see example)
If an additional name is added to the list in Row 7 in the original list, the total is now in row 8 - how do I ensure that the linked sheetl picks up this addition?
(I have a project where I am drawing data from numerous worksheets. I have paste linked them as tabs to a summary worksheet so that funtions such as SUMIF can update when the file is closed or open)
I keep a client list and excel at random will essentially hide entire rows of entries. For example if I go to search a name from the name column that I know I entered nothing will return but if I specifically enter the cell number that is hidden the information will be show, does excel 2011 have an auto-grouping or hiding feature that I'm not aware of? If so, how do I get rid of it? I'm not sure if this makes a difference but all the numbers to the left most of the sheet are blue where in every other sheet they are gray.
I am trying to create a drop down list from a named range that has duplicate names listed. I cannot delete any of the duplicate rows. Is there an easy way to create the list with each name represented only once?
I have a list of entries of which some are distinct and others are similar. For example, I have 1) JohnSmith 2) JhonSmith 3)SomeoneElse. The first and second entry are similar but not equal due to a spelling mistake, the third entry is not related to the first. My question is: How can I obtain which entries belong together? The output would have to show that 1&2 are similar and that 3 is different.
I am using excel 2003 to count my inventory in the following manor:
I have a "database sheet" that is comprised of possible inventory SKU numbers.
I have a "scanned sheet" that lists all the SKU's that I have scanned in the warehouse
I have a "count sheet" that counts the number of times each SKU in the database appears in the "scanned sheet"
Am I going about this in the most efficient manor?
Question 2: Frequently, new Items appear in the warehouse that are not yet in my database. Is it possible to create a list of SKU numbers that do not have a match in the database? As it stands, any SKU numbers that are scanned but not already listed in my database - simply do not get counted.
I'm trying to make a sheet that will allow users to either pick from a drop down box, or if the choice they want isn't there, to input data in the same cell. That part I have got. What I can't do is figure out how to have excel add that user input to the list of options, so that they next time the drop down box is used, that "user input" will appear as an option in the drop down. I used data validation to do all these drop downs.
I have a list in Row A that has duplicates. I want to be able to delete both entries (itself and the duplicate). When done I want the list to display only be entries that are unique or better said any entries that never had a duplicate.
