Search Results

Search found 67192 results on 2688 pages for 'excel external data'.

Page 63/2688 | < Previous Page | 59 60 61 62 63 64 65 66 67 68 69 70  | Next Page >

• grouping by date in excel and removing time in a pivot table

  - by Ashley DeVan

  My data looks like this: count Added Date 1 8/26/09 3:46 PM 2 8/21/09 6:50 PM 3 8/21/09 3:04 PM 4 8/21/09 3:21 PM 5 5/1/09 6:56 AM 6 5/1/09 8:12 AM 7 5/1/09 8:00 AM 8 5/1/09 8:18 AM 9 5/1/09 8:58 AM 10 5/1/09 8:58 AM 11 5/1/09 9:06 AM 12 5/1/09 9:44 AM 13 5/1/09 9:50 AM 14 5/1/09 11:17 AM 15 5/1/09 11:27 AM 16 5/1/09 11:29 AM 17 5/1/09 11:39 AM 18 5/1/09 12:10 PM 19 5/1/09 12:33 PM When I do a pivot table, I cannot get it to sum by day, it breaks it up by minute. I've even tried parsing the field, but the time always creates an issue. How to I get my pivot table to give me a count by day and ignore the time stamp?

  Read the article

• Automatically keeping two excel data tables in-sync (w/out VBA)

  - by Neil

  I'm putting together a workbook for tracking a stock portfolio. The primary sheet contains a table with the list of the transactions. From this I would like to create an overview table on another sheet with only one row per unique stock symbol that includes things like cost basis, returns, etc. The problem is that nothing I've tried updates the overview table correctly when rows are added to the transaction table. The closest I've got is something like the following: http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from-a-column/ However, this requires applying that formula to every cell in the primary column of the overview sheet. And even then the range of the table isn't extended down to include new rows as they become valid. Essentially I'm looking for a way that auto-adds rows to a table and copies the formula based on a different table changing without using VBA. Trivial example data Sheet1 Symbol Type Shares Price F Buy 100 12 MSFT Buy 100 25 MSFT Sell 50 28 F Buy 100 16 Sheet2 Symbol Quantity F 200 MSFT 50

  Read the article

• Macros Excel 2007 - extracting data

  - by Martin

  Im trying to extract certain data from a cell and trying to put it somewhere else within the same cell. Any suggestions? EDIT I have several text strings in ONE cell (a date, a part number and a color). The format looks like this 100906 PBO5 BLUE. The date is always the same number of characters but the part number could be 2, 3 or 4 characters. I want to move the date to the position after the color so it looks like this PBO5 BLUE 100906. I have over 1,000 records so I don’t want to do this manually.

  Read the article

• Vlookup to retrieve an ID from table using text match

  - by Federico Giust

  I've got an excel spreadsheet where I would normally use a VLOOKUP. In this case I need to find the ID of the record when comparing email addresses, so the email address is the unique id here. For example on sheet 1 A B C D Person Id | Family Name | First Name | Email #N/A | Doe | John | [email protected] On Sheet 2 A B C D Person Id | Family Name | First Name | Email 12345 | Doe | John | [email protected] Basically on sheet 1 I've got 800 records, on sheet 2 450. I know the 450 are in Sheet 1, so I need to find the ids of those, and put them on sheet 1 where I've got lots more data for each person. What I've tried so far is a VLOOKUP, but I keep getting an error. I'd like to do it with some sort of formula and not using any copy paste and remove duplicates. Any ideas?

  Read the article

• How to plot survey results on Excel?

  - by Mulone

  Hi all, I would like to plot the average of these data sample: 1. Contribution to the project 2. Affiliation with project owners 3. Level of expertise 4. Learning Curve Yes No 3 2 No No 3 2 No No 4 3 No No 5 3 No No 3 3 Yes Yes 5 4 No No 4 3 No No 3 2 No Yes 2 4 No No 5 2 No Yes 5 No answer No No 2 2 No No 3 3 ... Basically I know how to do the average of each numeric column, but I have a few questions for you: If the column contains non numeric values (e.g. "no answer") the average doesn't work. Is there a workaround for this? How can I count the yes/no results and plot only a percentage? Thanks for any hints! Mulone

  Read the article

• Using optimization to assign by preference

  - by Aarthi

  I have 100 objects ("candies") that I need to distribute between five people so that each has an equal number of candies (in this case, 20 candies per person). However, each person has also expressed their preferences of candy to me in a chart, similar to below. Top-favored candies receive 10 points, least-favored candies receive -10 points, and neutral-favored candies receive 0.5 points. I need to sort the items out so that: Each person receives the same number of candies Each person's total "satisfaction" (points) is maximized My output is a list of each person's assigned items I'm familiar with Excel's in-house Monte Carlo simulation tools (Solver, F9 diceroll, etc) and would like to stick to those tools. While I know how to set up the chart, and how to use the column summation to input into Solver, I don't know how to get it to give me the desired output. Furthermore, how do I adjust the solver so it takes into account individual preferences rather than empirical ones? To wit: how do I begin setting up this model?

  Read the article

• How to require a cell input if another cell has a value

  - by Connor

  I'm trying to edit the VBA for one of my workbooks so that If there is a value in column A, then a value for column C is required or else the file won't save, but i'm having some trouble with this. I can get excel to check a cell for any input and require it have input, but I need it to check if a different cell has an input before requiring input. This is because not all of the lines in my spreadsheet will be used all of the time, but some people forget to put very important pieces of information in the spreadsheet which throws some of our balances off. An SKU is entered on the sheet when we switch to a new one, and I want the program to make sure there is an amount of product in a given cell every time a new SKU is entered onto the sheet. Thanks.

  Read the article

• Excel 2010 Move data from multiple columns/rows to single row

  - by frustrated529

  So frustrating! I get data sent to me and it looks like this: a 1 a 2 2 a 3 3 b 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 and I need it to look like this: a 1 2 2 3 3 b 1 2 2 3 3 4 4 5 5 6 6 I have about 30 columns that need to move to the top value in their group, then removing the duplicates (to which there are about 33 rows of duplicates, trying to get it down to about 8 rows). I have been searching forums for several days and trying bits and pieces of code. I am having such a tough time with VBA!!!! Same illustration, but graphically:     →

  Read the article

• Excel Matching problem with logic expression

  - by abelenky

  I have a block of data that represents the steps in a process and the possible errors: ProcessStep Status FeesPaid OK FormRecvd OK RoleAssigned OK CheckedIn Not Checked In. ReadyToStart Not Ready for Start I want to find the first Status that is not "OK". I have attempted this: =Match("<>""OK""", StatusRange, 0) which is supposed to return the index of the first element in the range that is NOT-EQUAL (<) to "OK" But this doesn't work, instead returning #N/A. I expect it to return 4 (index #4, in a 1-based index, representing that CheckedIn is the first non-OK element) Any ideas how to do this?

  Read the article

• Comparing, merging, calculating colums of data in Excel

  - by hickster

  I would like to create a formula that a) compares four columns of data (see below) Sep Oct name units name units apple 2 apple 3 pear 3 pear 7 orange 4 banana 6 banana 3 toffee 5 then b) merges the two "names" column into one column, dropping any duplicates but still retaining the two unit columns (for months Sep and Oct) Sep Oct name units units apple 2 3 pear 3 7 orange 4 0 banana 3 6 toffee 0 6 then c) creates a third column that compares "Sep units" against "Oct units" and produces the total in the "difference" column Sep Oct name units units difference apple 2 3 1 pear 3 7 4 orange 4 0 -4 banana 3 6 3 toffee 0 6 6

  Read the article

• Two related cells: give a value in one, calculate the other, and vice versa?

  - by Virtlink

  How can I have a cell that uses the literal value written into it, or calculates its value when no literal value was given? For example: I have two columns: column B with a price including VAT, and column C with a price without VAT. If I put a price with VAT in B2, then I want cell C2 to calculate the price without VAT based on B2. But if I put a price without VAT in C2, then I want cell B2 to calculate the price with VAT from C2. I want to give this spreadsheet to my mother, who barely understands Excel. She just has to enter the values that she knows, and the worksheet should derive the other values from that.

  Read the article

• excel 2010 search function?

  - by Tom

  can a cell A1:A200 be searched for a "name" then once found, imput the cell location into a formula? such as find "tom"(a1:a200), [found location at cell a22] IF(a22),=IF(MINUTE(Auto_Agent!G27)+(SECOND(Auto_Agent!G27))=0,"",(MINUTE(Auto_Agent!G27)*60+(SECOND(Auto_Agent!G27)))) the problem I'm having is each time I import data names can be in different cell locations depending on who is working that day. example: Agent: Tom 07:59:49 02:31:04 00:00:00 00:42:44 01:33:02 00:00:43 00:02:00 03:09:05 Avg Skillset Talk Time: 00:06:52 07:59:49 02:31:04 00:00:00 00:42:44 01:33:02 00:00:43 00:02:00 03:09:05 () 9/19/2012 Avg Skillset Talk Time: 00:06:52 07:59:49 02:31:04 00:00:00 00:42:44 01:33:02 00:00:43 00:02:00 03:09:05 Agent: Bill 07:59:49 02:31:04 00:00:00 00:42:44 01:33:02 00:00:43 00:02:00 03:09:05 Avg Skillset Talk Time: 00:06:52 07:59:49 02:31:04 00:00:00 00:42:44 01:33:02 00:00:43 00:02:00 03:09:05 () 9/19/2012 Avg Skillset Talk Time: 00:06:52 07:59:49 02:31:04 00:00:00 00:42:44 01:33:02 00:00:43 00:02:00 03:09:05

  Read the article

• Excel Pivot table: Calculated field based on only the first row of a group

  - by Meysam

  I've got the following data and pivot table: The Total column in the pivot table is the sum of the following calculated field: =start-TIME(7, 30, 0) I know that this calculation is wrong for what I want to achieve. I need to know how much delay I have had on each day to start the work. e.g. on 1-Oct-12, assuming I should have started my work at 7:30, 8:00 - 7:30 which yields 30 minutes delay, 1 hour delay for 2-Oct-12 and 50 minutes for 3-Oct-12. So my question is, how can I have a calculated field based on only the first row of each group in a pivot table?

  Read the article

• When an Excel file is locked on a network share, how does it know who locked the file?

  - by ScottStonehouse

  We are using Excel 2010 and sometimes users are working from a network share. When a user on the network opens the file in Excel, the file is locked for editing - this is the expected behavior. When another user tries to open the file, Excel gives the message [filename] is locked for editing by 'John Doe' The problem is, the user is always this one particular user's name, no matter who actually has the file open. Where does Excel get this info, and how do I fix it? Update: I should have mentioned that in Excel, under Options (General), the correct user names have been set up. That is not where this user name is coming from.

  Read the article

• Excel VBA Function runtime error 1004: Application-defined or object-defined error

  - by music2myear

  I'm trying to learn functions for the purpose of simplifying and reusing code whenever necessary. I began by turning something I use pretty often into a function: Returning the integer value of the last non-blank row in a spreadsheet. Function FindLastDataLine(strColName As String) As Long FindLastDataLine = Range(strColName).Offset(Rows.Count - 1, 0).End(xlUp).Row End Function Sub PracticeMacro() intItemCount = FindLastDataLine("A:A") MsgBox ("There are " & intItemCount & " rows of data in column A.") End Sub When I run this I recieve the runtime error '1004' "Application-defined or object-defined error" which Help helpfully defines as "someone else's fault" to quote not quite verbatim. Where might I be going wrong?

  Read the article

• Can Excel show a formula and its result simultaneously?

  - by nhinkle

  I know that it's possible in Excel to toggle between displaying values and displaying formulas. I'm required to turn in assignments for a statistics class as a printed Excel sheet showing both the formula and the result. Right now the instructor makes us either copy the formula and paste it as text next to the computed value, or copy the value and paste it next to the formula. This is very inefficient, prone to error (if you change the formula or values after doing the copy-paste), and generally a waste of time. Is there any way to have Excel show the formula and its value in the same cell? If not, is there any function which will display the formula from a referenced cell as plain text, e.g. =showformula(A1) which would print out =sum(A2:A5) instead of 25 (if those were the formula and value of cell A1)? I'm using Excel 2010, but a general answer that works for any recent edition of Excel would be nice.

  Read the article

• MS Excel find and replace macro

  - by william

  I have written a macro to remove special characters in a sheet based on ascii values but the problem with it is that it is replacing the cell content. For example p;j;h which should become p,j,h is becoming ,, (missing the data). Do I need to include any additional statements, or how else to adjust my code? sub specialcharecters() Dim i As Long For i = 32 To 43 Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next i Selection.Replace what:="~*", replacement:=", ", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False For i = 45 To 47 Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next i For i = 58 To 64 Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next i For i = 123 To 125 Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next i Selection.Replace what:="~~", replacement:=", ", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False END sub

  Read the article

• How can I fix a date that changes by 4 years and 1 day when pasted between Excel workbooks

  - by lcbrevard

  In Excel dates are represented internally by a floating point number where the integer part is the number of days since "some date" and the fractional part is how far into that day (hence the time). You can see this if you change the format of a date - like 4/10/2009 to a number 39905. But when pasting a date between two different workbooks the date shifts by 4 years and one day!!! In other words "some date" is different between the two workbooks. In one workbook the number 0.0 represents 1/0/1900 and in the other 0.0 represents 1/1/1904. Where is this set and is it controllable? Or does this represent a corrupted file? These workbooks where originally from Excel 2000 but have been worked on now in Excel 2007 and Excel 2003. I can demonstrate the problem between the two workbook files in both 2003 and 2010. The exact history of when they were created or what versions of Excel have been used on each is unknown.

  Read the article

• Excel Matching problem with logic expression

  - by abelenky

  I have a block of data that represents the steps in a process and the possible errors: ProcessStep Status FeesPaid OK FormRecvd OK RoleAssigned OK CheckedIn Not Checked In. ReadyToStart Not Ready for Start I want to find the first Status that is not "OK". I have attempted this: =Match("<>""OK""", StatusRange, 0) which is supposed to return the index of the first element in the range that is NOT-EQUAL (<) to "OK" But this doesn't work, instead returning #N/A. I expect it to return 4 (index #4, in a 1-based index, representing that CheckedIn is the first non-OK element) Any ideas how to do this?

  Read the article

• Excel conditional selection?

  - by Andrew

  I think this is a simple question. I have a big table of data points and I want to take a an average of a subset of a single column. For example, if A is "age" and B is "gender," what command could I use to calculate the average age of women in my table? I know I can do this by sorting the table by column B and then only selecting column A values, but I want to build up to much more complicated conditional terms (e.g. if A is 5 and B is 3 and C is 4, then give me the average of D). Thanks!

  Read the article

• Is there a way to get a shared spreadsheet to update without closing and reopening?

  - by Mike

  Using Excel 2010, I have a spreadsheet that is used by 3 different people at any one time. But if one person has the spreadsheet open on there PC the other people can only view it as read only. I have since shared the workbook and put the spreadsheet on a shared network drive and now they can all view the spreadsheet at the same time and edit it at the same time. The problem is that nobody can see the changes that the other users have made unless the close out of the spreadsheet and open it up again to view the changes. I have checked the settings of the shared workbook and on the advanced tab have tick the option that updates the information every 5 minutes but the information does not update until you close out and open the spreadsheet back up again. How can I fix this problem?

  Read the article

• Fill rows down quickly (column or matrix of zeros)

  - by Mark Miller

  I have an extremely basic question, but I have never found the answer by searching the internet. I simply want to create a large column of zeros with Excel. Sometimes I want to create a huge matrix of zeros (maybe 600 rows by 500 columns) and then replace a few zeros with 1's to create a model design matrix. I have always started by creating a column of, for example, 10 zeros, copying and pasting those zeroes, then copying and pasting the resulting column of 20 zeros, etc., until I had the desired number of rows. Then I would copy and paste that column of zeros one at a time over and over until I had the desired number of columns. This procedure is tedious and time-consuming and I know there must be an easier way. Do you know of any other methods?

  Read the article

• How do you create a report in Word (or other documentation software) that is linked directly with Ex

  - by NoCatharsis

  I believe my question may be best answered by using Access since that's more what it's made for. However, I don't have a license for Access here at work and trying to get one is pulling teeth. So I'm curious if there is any way to compile reports with data in an Excel 2007 sheet. The output can be .doc, .docx, .pdf - or anything else if there's a decent piece of free 3rd party software. This might be easiest solved by just creating another sheet in the same workbook and directly linking to the data I want to display in a report-esque format. But I wanted to see if SU could offer some more creative solutions.

  Read the article

• Excel 2010 Move data from multiple columns to single row

  - by frustrated529

  So frustrating! I get data sent to me and it looks like this: a 1 a --2 2 a-------3 3 b 1 b-- 2 2 b ------ 3 3 b------------ 4 4 and i need it to look like this: a 1 2 2 3 3 b 1 2 2 3 3 4 4 I have about 30 columns that needs to move to the top value in their group, then removing the duplicates. I have been searching forums for several days and trying bits and pieces of code. I am having such a tough time with VBA!!!!

  Read the article

• Macro to copy values to new sheet and prompt user where they want to save

  - by MasterJedi

  I'm looking to create a macro in Excel 2007 which will do the following: copy the formatting and values from a sheet called 'report', insert this into a new workbook, naming the file with the value in 'B9' and appending the word 'report' to this when copying the data into a new workbook, the formatting of the original should be retained, but cell contents pasted as values rather than formulas prior to saving the file with the above name, the user will be prompted to choose their folder at the end, a message will be displayed alerting the user that the file has been saved successfully Any assistance would be much appreciated. Thanks. I have attempted to do this, but my experience with VBA is limited. Here is what I have done so far... Application.DisplayAlerts = False Worksheets("Report").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.DrawingObjects.Delete Set wbNew = ActiveWorkbook wbNew.SaveAs Application.GetSaveAsFilename wbNew.Close True Application.DisplayAlerts = True

  Read the article

< Previous Page | 59 60 61 62 63 64 65 66 67 68 69 70  | Next Page >