Search Results

Search found 4783 results on 192 pages for 'excel vba'.

Page 51/192 | < Previous Page | 47 48 49 50 51 52 53 54 55 56 57 58  | Next Page >

• Excel formula: can MATCH recognise 'n'&"01", or 'n'&"02 "

  - by Mike

  I have an Excel sheet (source) that has simple ID numbers in column A (01 to 40000). In another sheet (child) I have these same ID numbers in column A but with either an additional 01 or 02 added on; e.g. 0101 or 0102, 250001 or 250002, etc. Therefore this list of ID numbers is nearly twice as long. In column B there are figures. I'm trying to extract the data from column B in the child sheet, and based on whether it has a "01" or a "02" place the figure into either column B or C of the source sheet. My idea is to use INDEX/MATCH, but I'm not sure how the match would be written to take into account the NOT EXACT MATCH of the lookup value. MATCH(A1&"01",child!A1:A100000,). Any tips and links greatly appreciated. Mike.

  Read the article

• Excel formula - sometimes array formula, sometimes not

  - by Jonas

  I want to write some data to an Excel file, and I want the first page to show a summary of the data that are spread all over the sheets. The individual data items on a sheet can be included or excluded, and I want the summary to be calculated only on the included values. Thus, I thought I'd use a formula like this to show, for example, an average (for which empty fields shouldn't be counted, either): =AVERAGE(IF('dataSheet1'!$B:$B=1,IF('dataSheet1'!$I:$I<>"",'dataSheet1'!$I:$I,""),"")) If I have this formula in dataSheet1, everything works fine. If I have this formula in the summary sheet, I need to enter it as an array formula, and I have to specify the exact ranges to make it work. Is there a way (aside from adding the average in dataSheet, and then referencing the result in summary) to make the formula work without having it turn into an array formula?

  Read the article

• Excel: count number of unique/distinct row in range with condition

  - by Bertvan

  I have a an excel sheet with: in Col A: week numbers in Col B: dates (timesheet entries) I need to know the number of days worked for each week, so I need to the number of unique date entries per week number. I found formula's (both array as non-array) that handle this for a fixed range, but I want to have the results in another column per week number. So, the result of the added dataset below would be (the colon is just for clarity): 14: 2 15: 3 17: 6 20: 2 21: 3 If this is the source data: 14: 4/04/2012 14: 4/04/2012 15: 10/04/2012 15: 10/04/2012 15: 11/04/2012 17: 26/04/2012 17: 26/04/2012 17: 26/04/2012 17: 26/04/2012 17: 27/04/2012 17: 27/04/2012 20: 14/05/2012 20: 14/05/2012 21: 23/05/2012 21: 23/05/2012 21: 25/05/2012

  Read the article

• Automatically select last row in a set in Excel

  - by Luke

  In Excel 2003, I am trying to keep track of some petty cash, and have it set up with the denomination along the top row, along with a sub total and difference column. I want a small section that shows how many rolls of coins I should have, by taking the total amount, and dividing it by however many should be in a roll, and rounding to the lowest whole number. That part is fine. What I want done is for that ONE section (how many rolls) I should have, based on the last row that has information in it. For example, if the last row is row 13, it should read the data from B13, C13, D13, etc. I don't mind learning Macros, if that's what the solution requires. I don't want to be manually selecting the last row each time though, I just want the worksheet to know automatically.

  Read the article

• Sorting/grouping when there are multiple values in one cell

  - by ngm

  I have an Excel 2007 spreadsheet, where each row of the dataset describes a feature of a piece of software. One of the columns in the spreadsheet is Relevant Users, which describes which users of the software the feature is of interest to. There may be a couple of different users interested in a feature, in which case I've been filling in the cell with the two user types separated by a colon, e.g. 'Usertype A; Usertype D'. Occassionally, I'd like to sort my data by the Relevant Users column. However, the way I'm populating the column means the sorting isn't very smart. If I have a feature where 'Relevant Users' is 'Usertype A; Usertype D', and then I sort by Relevant Users, that feature will be grouped at the end of all the other features of relevant to Usertype A, as it's just sorting alphabetically. But I want it to be listed in the two separate groups of Usertype A and Usertype D. Or, if I have a pivot table that groups the features together under the heading of Relevant User, I'll get all the features for 'Usertype A', then 'Usertype B', then 'Usertype C', then 'Usertype D', then 'Usertype A; Usertype D', etc. Whereas I really want a feature with Relevant Users as 'Usertype A; Usertype D' to show up in both the Usertype A group and the Usertype D group. I guess if this information was in a database I might have a many-to-many table linking Relevant Users to features. But is there a way to go about having this kind of many-to-many relationship in Excel?

  Read the article

• How do I change the format of group by data in Excel 2003 pivot tables

  - by Bernard

  I have a data that lists the value of a contract per contract number. So I've created a pivot table that counts the number of contracts valued at 0 - 10, 11 - 20, etc. I want to be able to format the group, e.g. $0 - $10, $11 - $20 I've tried formatting the underlying data as currency and formatting the column in the pivot table, but it still shows as 0 - 10, 11 - 20 Also I have a column in the pivot table that says Total which is the Count of the number of contracts in that range, i.e. Value Total 0 - 10 1 11 - 20 1 Its an autogenerated column heading that Excel put in. How do I change this to say Number of contracts. I want it changed because when I chart the pivot table, the series is called called Total :(

  Read the article

• Double Filter in Excel

  - by Joe

  I'm trying to "stack" filters in excel, so to speak. I want to filter column A to show anything greater than 30 and then I want to filter column B to show the top ten items. When I do this, however, it shows me all rows that fit both criteria (only five records). I want to first fit the criteria for column A and then filter these results to show the top ten items in column B (10 records total). I know that I could just copy the rows from my first filter to a new sheet and then filter the new worksheet, but is there any way to apply both filters so that I don't physically have to delete records this way? Thanks for your help!

  Read the article

• Can't import contacts into Outlook from Excel

  - by Michael

  I run MS Office 2010. I have all contacts from a previous Outlook 2010 saved in an Excel sheet. I have tried dozens of time to import, but when Outlooks is supposed to do the mapping of the columns I only have one column in my CSV-file: firstname;middlename;surname, etc. It means I cannot map the data correctly. I have tried saving in MS-DOS and Tab and it does not get better. I have tried saving on another computer and it does help either. ALso tried importing to Gmail with same result. Can anyone help?

  Read the article

• multiple count Pivot table in Excel

  - by Sivakanesh

  Hi all, I'm trying to put togeter a pivot table from an Excel spreadsheet. The spreadsheets look similar to the following: DeptHead, Emp, Increment x, A, 2.5% x, B, y, C, 1.5% y, D, y, E, 2.0% I would like to make a pivot table that looks like the following; DeptHead, CountOfEmp, CountOfIncrement x, 2, 1 y, 3, 2 So it provides a count of total number of Emps and total number Increments for each DeptHead ignoring the blanks. I have tried to do this in many ways in Pivot table, but the two counts are only appearing in rows and not in columns as above. Is there any way to achieve this please? Thanks

  Read the article

• Is this way of using Excel 2007 Pivot table for BI scalable ?

  - by Sim

  Hi all, Background: We need to consolidate sales data across the country to do analysis Our Internet connection/IT expertise/IT investment is not quite strong, therefore full BI solution is out of question I tried several SaaS BI solution (GoodData, ZohoReports) and while they're good, they seem not to fully support what we need We're looking at 'bout 2 millions record for every 2 months My current approach Our (10) sites currently gathers data from all their branches and consolidate them into 1 Excel file with Pivot table and embed source data In HQ, I will request 10 sites to send back those Excel files periodically We will import those Excel to our MSSQL server There will be a master Excel file, that will also have the same pivot table (as those came from site Excel file), and datasource is the MSSQL server More details For testing, I currently use MSSQL 2008 Express on my laptop So far, I imported our transactions for the past 2 months and there are 2 millions+ row in 1 table in MSSQL (we just use 1 table, corresponding to our common pivot table structure). DB size is ~ 600 MB In the master Excel file, if not including the source data, it's just < 10MB. Including the source data will increase the size to 60 MB (so I supposed Office 2007 automatically zip the data ?) I try using the Pivot (drag-and-drop fields) and the performance so far is OK (my laptop specs: C2D T7200, 3GB RAM, Windows XP) So my question is : If we're looking at full year transaction (roughly 15 millions rows in MSSQL 2008 Express, 3.6 GB in size), is there any issue with that 15 million rows in 1 table in SQL Express ? Is there any performance issue with the pivot table at that time ? Can it still embed the source data ? (I google-ed but didn't find the maximum size of source data Excel 2007 can embed) Any other suggestions on how we can better do this ? Given that we can't afford the full BI solution, any light-weight/budget/SaaS BI that you can recommend ? Thanks

  Read the article

• Excel: Conditional Formatting (Highlighting) Values Based on Another Worksheet

  - by ScottSEA

  I have a workbook that has two worksheets. The first worksheet is simply a list of the first 78,498 prime numbers in a single column, A1-A78498. The second worksheet has a grid of numbers from 1 to n. The goal is to highlight the cells with prime numbers in the grid by referencing the prime number values in the other worksheet. Is this possible, and if so, how? edit I have named the column with my prime numbers "PRIMES1T". I would like the formula to work for the entire worksheet, regardless of size, but my excel-fu is extremely weak. If at all possible, I would like to be able to enter the formula in the dialog box for conditional formatting (as below): I have tried =NOT(ISNA(MATCH(A:Z,PRIMES1T,0) (only A-Z, but have to start somewhere) with no luck.

  Read the article

• Script/ Macro in Excel, by clicking on a cell

  - by Noob Doob

  There's something I want to do in Excel 2010. Specifically, I want to be able to make the open sheet load data from a text file, by clicking -important- on a cell of the current open worksheet. So my specific needs are: If it is possible in some way, to start a macro/script by clicking on a cell. About the script/macro: What would be the preferable, by your opinion, way of implementation. And more specifically, how to automatically import the data needed, only by clicking, without having to continually specify the desirable .txt file each time, only 1 time at max, at start or by using a custom "initalization/configuration" file.

  Read the article

• Excel trendline accuracy

  - by Rook

  This is a problem I have every once in a while, and it annoys me tremendously, beacuse I have always to recheck every trendline I get. An example: r L (mm) 30,00 97,0 60,00 103,2 90,00 106,0 110,00 101,0 125,00 88,0 140,00 62,0 148,00 36,7 152,50 17,0 Upon drawing a trendline (using 3rd order polynomial regression type) with r on the x axis, and L on the y one, Excel will give the formula y = -0,0002x³ + 0,0341x² - 1,8979x + 128,73 with R² = 0,994. If I interpolate values using that formula for the same values of r as the ones the formula was derived from, I get r y (mm) 30,00 97,083 60,00 94,416 90,00 88,329 110,00 66,371 125,00 33,68 140,00 -17,416 148,00 -53,5912 152,50 -76,97725 which are quite different? Why does this happen? What is the reason for it?

  Read the article

• Excel Pivot Tables -- Divide Numerical Column Data into Ranges

  - by ktm5124

  Hi, I have an Excel spreadsheet with a column called "Time Elapsed" that stores the number of days it took to complete a task. I would like to make a pivot table out of this spreadsheet where I divide the "Time Elapsed" column into ranges, e.g., how many tasks took 0 to 4 days to complete how many tasks took 5 to 9 days how many took 10 to 14 days how many took 15+ days Do I have to create new columns in my spreadsheet dedicated to each interval (0 to 4, 5 to 9, etc.) or can I use some feature of pivot tables to separate my one "Time Elapsed" column into intervals? Thanks in advance.

  Read the article

• Simple (I hope) Excel question about

  - by Princess

  I am doing a directory for my neighborhood. We had most of the information from a previous directory. The information was entered: A1 name, B1 address and C1 phone number; B1 name, B2 address, C2 phone number etc. The publisher wants the information in a different format A1 name, A2 address, A3 phone number, A4 blank; A5 name, A6 address, A7 phone number, A8 blank etc... Is there an easy (or heck - a not so easy) way to have Excel change the format of the information without me having to hand type 1300 households information? I will also need to reformat the information a second time into a crisscross. The format for that one is: A1 Street name, A2 Address Number, B2 Resident Name and C2 Phone number.

  Read the article

• Excel 2007: Exporting more than 100 columns to a .prn file but data is concatenated

  - by Don1

  I want to export an Excel worksheet to a space delimited (.prn) file. The worksheet is pretty big (187 columns) and when I set the column widths and try to export the worksheet to a .prn file, the data gets cut at the 98th column (i.e. about 200 characters wide for my data) and the rest is placed directly underneath. It's like I ripped a page in half from top to bottom and placed the right-hand side directly under the left-hand side. How would I get it to export everything without getting concatenated?

  Read the article

• How to calculate Bradford Factors with Excel 2007?

  - by pnuts

  Bradford Factors are used by some to measure the significance of absenteeism and are computed for each individual as S squared * D where S is the number of spells (continuous periods of absence) and D is the sum of the days. The calculation is often made over a rolling 52 weeks. Commercial HR software often has the facility to calculate these factors but a Google search indicates quite a lot of interest without any free solutions. Using units of half a day and including any non-working days in each spell, how does one calculate the factors using Excel 2007?

  Read the article

• Ms Excel 2010 Importing Data in One ROW and getting sum particular CELL

  - by Omeshanker

  I am importing data by using .txt file to MS Excel and whole data is imported in ONE ROW. I want to get SUM of those values which corresponds to a particular Month. For Example :- Name Month Total Value Mark Jan 2000 Mark Jan 1500 Mark Feb 2900 Mark Feb 3000 I want to get the TOTAL value in the Month Jan in a particular Cell. Kindly tell me how to proceed. NOTE: Whole data is imported in one ROW only. So the formula should add automatically those values which it finds out on the row. Thanks Omesh

  Read the article

• Excel transpose via paste

  - by David Oneill

  I want to transpose data in Excel. Normally, I cut the cells I need, and use paste special - transpose. However, sometimes when I do paste special, a box comes up asking me if I want to use unicode text vs normal text. How do I transpose this text? Is there a way to get past the unicode dialog box and get to the normal Paste special dialog box (that has the 'transpose' option)? Or, is there another simple way to transpose cells? transpose = flip rows and columns IE 1, 2, 3 becomes: 1 2 3

  Read the article

• How to remove Excel List Manager?

  - by jdmuys

  I have a spreadsheet with columns set as using the List Manager. I want to remove the list manager. However, the "Remove List Manager" item in the "List" menu of the List toolbar is always disabled. I tried selecting a number of different set of cells in the list, to no avail. "Remove List Manager" stays stubbornly disabled (grayed out). What am I missing? I am using Excel 2008 version 12.1.5.

  Read the article

• Microsoft Excel IF/THEN statement for Words

  - by user1667462

  Right now I have an excel spreadsheet. In Cells A1 and down I have cities listed. In Cells B1 and down I have some generic information with the word CITY in it. What I want to know if there is a formula to replace the word CITY that is in the contents of Cell B1 with the contents in Cell A1. For instance... Cell A1 has "Daytona Beach, FL" Cell B1 has "Compare mortgage & refinance rates from different mortgage lenders and brokers in CITY. Find the home refinance rate you were looking for in CITY." I need a formula that replaces "CITY" in B1 with "Dayton Beach, FL" in A1. Is this possible? Thank you for your help!

  Read the article

• Conditional Lookup in Excel

  - by Keyslinger

  I want to use excel to compare pairs of numbers from the "Pre/Post" column of the following data: Student Course Pre/Post Score K300997203 FHS120100417 Pre 3 L286197217 FHS120100417 Pre 5 S106497203 FHS120100417 Pre 4 K300997203 FHS120100417 Post 4 L286197217 FHS120100417 Post 4 S106497203 FHS120100417 Post 4 S106497203 FHS220100424 Pre 4 Specifically, I want a cell to contain the difference of the value in the "Score" column where "Pre" and "Post" appear, respectively, in rows with the same value in the "Student" and "Course" columns. For example, Student K300997203 has a row containing Course FHS120100417, a score of 3, and "Pre" AND Student K300997203 has a row containing Course FHS120100417, a score of 4, and "Post". How can I calculate a cell value as the score in the row containing "Post" minus the score in the row containing "Pre"?

  Read the article

• Date based sum in Excel / Google Docs spreadsheets

  - by alumb

  I have a bunch of rows with a date and a dollar amount (expenses). I want to produce a list of the days of the month and what the balance of the expenses is. So, for example the 5th entry in the list would be 8/5/2008 and the sum of all the expenses that occurred on or before 8/5/2008. Approximately this is =sumif(D4:D30-A5,">0",E4:E30) but of course that doesn't work (where the source data is dates in D4:D30 and the expenses are in E4:E30). Notes source data can't be sorted for various reasons. must work in google spreadsheets, which is a fairly complete subset of excel's functions.

  Read the article

• In Excel format number with optional decimal places

  - by Ray

  Simple: I have numbers in cells in excel. I want the numbers formatted so that if they have decimal places they show to a maximum of 2DP and if they have no decimal places it doesn't show any. For example. 15 should be formatted as 15 NOT 15.00 14.3453453 should be formatted as 14.35 12.1 should be formatted as 12.1 0 should be formatted as 0 The closest custom format code I've come up with is 0.##. Unfortunately this formats 15.00 as 15. (note the extra decimal place). Edit: To further complicate the issues, the spreadsheet is a result of an export from SQL Server Reporting Services. So no macros are possible. Oh well, it looks like 0.## is my best bet, and they can just live with the extra period.

  Read the article

• Excel Countif external date

  - by Duall

  I am making an Excel 2010 spreadsheet to log support calls, services, and installations that each member would fill out. Due to being paid by job rather than by hour, there is a need for it to count each of these ("Call", "Service", "Install") there is in any given time span. The entry of the data itself would be in Sheet 1, and then a 'splash screen' of sorts would be in Sheet 2. Here I would like to be able to put a date range and it would display how many of each there is. I already can do the COUNTIF statement, =COUNTIF(Activity!$B:$B,"Call") but I don't know how to: a) Add in the prerequisite for a date so it only gets "Calls" within a certain time frame. b) Take the date it looks for from a cell on the splash screen.

  Read the article

< Previous Page | 47 48 49 50 51 52 53 54 55 56 57 58  | Next Page >