want to do piovt table, where rows over 100,000
i have around 100,000 rows of data...
of cousres the worksheet i can only put 66000 or so rows per sheet.
i want to do a pivot table that combines the two sheets.
how can i do this?
Message posted from http://www.ExcelForum.com
if you have that many data I would suggest you store this data in a
real database (e.g. MS Access). With Excel's pivot table wizard you can
then access this database as datasource.
Another way would be to split the data in two worksheets and within the
pivot table wizard choose the 'non contigenous ranges' for the data
Reg...Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I
want to compute the internal rate of return (IRR) for each investment based
on a series of cashflows for each investment.
The IRR is a function that is placed outside the pivot table. As each
investment number is chosen, the underlying pivot table cashflow data
changes, allow the IRR function to pick up these cashflows and compute the
IRR. However, if there are 500 investments, this becomes very time consuming
- especially if the...Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a
ProposalForm, ServiceTypes need to be added to a ProposalServicesTable.
For instance, I have a Check Box on the ProposalForm. When a Check Box is
clicked Yes, Access must search the ServiceTypes Table, select a specific
ServiceTypeID, and add the ServiceType to the ProposalServicesTable.
How can I add the proper Service record from the ServiceTable to the
ProposalServicesTable based on the Check Box?
I wouldn't do it that way. I'd use a listbox (with multi-select set to YES)
that was sourced to the ServiceTab...Saving existing Outlook emails
I'm needing to re-format my harddrive and am wondering how
I can save my existing Outlook emails and settings so that
once I have the newly reformatted drive setup I can access
my saved emails and files?
do a search in your hard drive for *.pst... it contains all e-mails and
contacts... in brief, all eggs in one basket. If you have mail rules you can
export them, as you can your mail account(s).
<email@example.com> wrote in message
> I'm needing to re-format my harddrive and am wondering how
> I can ...pivot table #21
I have a question on Excel. When I update a pivot table,
I used to be able to hold down the shift and ctrl keys
and highlight the area, but lately I found that I cannot
use this short cut method. Is there another short cut
method? Thanks for your help.
In step two of the Pivot Wizard, you should be able to select a starting
cell on the worksheet, then hold the Shift key, and tap the End key,
then the Down or Right arrow key, to select a range of cells.
Or, base the pivot table on a dynamic range, which will expand
automatically as new records are added. There are instructions here:...transfer inbox in table format to word
In my older version I could cut and paste the table
format in Outlook to a word file. Now I don't seem to be
able to do it although I can print the file in that
format within Outlook. When I export the inbox to word I
get the whole text not just the headings. Is it possible
to transfer the table format to a word file?
...SMTP address list
Does anyone know of a way to obtain a list of all SMTP email addresses
accepted by a server and the corresponding user account in Exchange 2003?
You can use LDIFDE to dump Active Directory attributes to a text file. The
email addresses are held in the 'proxyAddresses' attribute, so:
ldifde -f dump.ldf -l proxyaddresses
This will give you a file called dump.ldf containing your addresses. You
can add other Active Directory attributes to the export file as you see fit.
For Exchange news, links, and tips, check:
http://www.msexc...pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th
pivot table function to reduce the data to hourly I need the date t
repeat in the date column.
9/19/2002 0:00 2.7
9/19/2002 0:10 3.7
9/19/2002 0:20 3.8
9/19/2002 0:30 4.6
9/19/2002 0:40 4.8
9/19/2002 0:50 4.8
9/19/2002 1:00 5.6
9/19/2002 1:10 5.2
What I get when I group the Pivot table:
Sep 19-Sep 12 AM 2.7
- - 1 AM 3.7
- - 2 AM 3.8
- - 3 AM 4.6
- - 4 AM 4.8
What I want is:
Sep 19-Sep 12 AM 2.7
- 19-Sep 1 AM 3.7...Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a
pivot table thus displaying a charted image of the detail item selected. Any
...problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change
the text of some sentences of an opened word file, but the problem is when I
change the text of a sentence which located before a table, it will be moved
to the first cell of the table. My code is as follow:
void myMethod( long startingSentenceNumber, const char *toBeSearched, const
char *replacement, bool replace )
Sentences sentencesList = m_document.GetSentences();
long sentencesCount = sentencesList.GetCount();
CString replacementCStr(...Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to
SQL 2000) to ASP.NET(using store procedure in SQL 2000).
Finally, I tested them and found that ASP.NET is slower than Access.
The mojority job of program is select some data from SQL 2000 tables, modify
and then insert into some tables.
Is store procedure always fast than Access linked table via ODBC?
Message posted via http://www.sqlmonster.com
Stored procedures don’t add any overhead and they can save compile time.
It's the code in the stored procedure and the underlying tables / indexes
that ...Create incident on website : localhost ok
when i open a new case in
it creates the "incident" normally
but when i open a new case in
Operation failed due to a SQL integrity violation.
System.Web.Services.Protocols.SoapException: Server was unable to process
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response,
Stream responseStream, Boolean asyncCall)
at System.W...Adding Members- Dist. Lists
Not sure if I should be posting this here or in the SBS group. Does anyone
know if there is a way to assign a permission to a user in our office to be
able to update membership lists for distribution groups while she is in
Outlook? Currently, when she clicks the "To:" button in a new email,
right-clicks the distribution group, selects properties, goes to "modify
members", it lets her select the new member but then when she clicks ok she
will get an error mssg saying she doesn't have permission to do this.
on the security tab of the DL, does she have the &qu...Creating a Check Box
I would like to learn how to create a check box. Is there
a way to do that?
The simplest checkbox (I think) is from the Forms toolbar.
So View|tool bars and show that Forms toolbar.
Click on the checkbox and then click on the topleft corner where it should be
placed. Then draw to the opposite lowerright corner.
When you've placed it correctly, right click on it and select "Format Control"
On the Control Tab, you can assign a cell link that you can use to determine if
the checkbox is checked or not.
=if(A1=True,"It's checked","It's not checked")
...odd files created
Every time I open and edit an excel spreadsheet on a
network share, small odd files get created. They are
usually no larger than 25-40k and don't have any
extensions to them. Looking at the properties page for
any file, the file description says File. Anyone know
what this is from or how to get rid of them? Permissions
are setup correctly for me, Word files don't have this
Excel 2000 SP3
A file the same size as the workbook would be created
in the same directory as the workbook. The filename
would be nonsensical (or appear to be random) character...Output the list of frequent data
Here comes two problems.
Problem 1: I have a list of strings (say, in the column A1:A100). How can
I find the "mode" (i.e. the string appearing most frequently in the list?
For example, if the list is
then I want the result is APPLE. It seems that the MODE function does not
suppot data type other than numbers. Is there any canned UDF for it?
Problem 2: Following Problem 1, I want to generate a list of the 3 most
frequent data in the list. If the list is the one in the example, I want to
list to be...Create Login with T-SQL
I am creating, using T-SQL, a database with its file groups, tables,
Can I also create, using T-SQL, a Login with Username and Password to
access that database?
Usually, I go to Security > Logins of SSMS to manually create the
However, if I could do the same using T-SQL I would have it all made
EVERYTHING that the GUI does winds up as tsql executions on the server. :-)
Most of them you can generate a script for using the Script menu in the
upper left of the current dialog window. This is a great way to ...How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart
that contains a horizontal target line. Can anyone help me?
I tried it in both 2007 and 2003 and had no problems. We need more
infomation, can you post a sample of the chart somewhere. The only issue I
have is that the target line is displayed on the data table, which I would
prefer that it not be.
> I am having trouble inserting a data table with legend keys into a line chart
> that contains a horizontal target line. Can anyone he...Evaluate Yes/No Field Based on User Input
Hi. I have a field that is set to Yes/No. I want to ask the user a question
and based on their response (whether they type yes or no) I want the query to
check the field and return all records marked yes is they type yes and all
other records if they type no. How can I do this?
Also, could I present them with a simple text box (having yes and no
choices) or maybe a check box so they won't have to type anything? If you
help me with the first part, this question is a bonus. I'll be happy with
just the first question answered.
A Yes/No field actually stores -...Creating Charts for Use in Powerpoint
I have a series of charts that I need to create for a Powerpoin
I thought it would be easy to just create the charts/graphs in Exce
and then just cut and paste to the PP-Slides. I'm finding that it isn'
quite that easy.
Here are some of my problems that I hope someone might be able to she
some light on and give me some direction.
1. In Excel how do you control the size of the chart?
- When I create a chart 1 and define the data ranges I get a goo
looking chart then I thouht, why reinvent the wheel so I just copy th
chart to create chart 2 and change the data range, but...Create Calendar mtg invite template to avoid retyp'g conf no's?
I want to create a calendar template so that I can avoid retyping my
conference call details in the location field everytime I create a new
invite. How do I do this?
In what version of OUtlook?
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
> I want to create a calendar template so that I can avoid retyping my
> conference call details in the location field everytime I create a new
> invite. How do I do this?...number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a
financial report. Actually the error popup says "A get/change operation
on table 'GL_Options_ROPT' failed accessing SQL data", the more button
reveals the number of columns error description. This database was
restored by copying the sql folder from a previous installation into the
new servers sql folder. Thanks.
shawn modersohn wrote:
> This is the error I get when among other things, I try to print a
> financial report. Actually the error popup says "A get/change operation
> on ta...Update table with Multi-select list box
I have a database with a tab control that has several pages in it (my
boss loves tabs for navigation). Each page has a list box based on a
category of training events that members of my office attend and the
box is based on a query that selects the training events for the tab
page's respective category. I select an item from the list, click a
button, and go to a form with a text box showing the name of the
training event I previously selected. On that form, I want to select
mutiple names of office members from a list box (already created and
source is the Office Roster table) and then c...Show / Hide items in Pivot Table as required
I am using MS Excel 2003 and need a macro code for show only my selected
items only in a Pivot Table.
One excel table empstatus.xls in which fields are Employee No , Department,
Status, other fields. In status field items are Blank, Leave, Left, Resigned.
I wanted to show only blank & leave based on when I’m selecting a particular
month in my pivot table.
So is there any code to hide all data items of status and only show blank &
...How do I create a chart in Excel using data from two worksheets
I am trying to create a chart using data from two separate worksheets but it
will only allow me to use the information from one.
Here is one way:
I made a chart from data in A10:B20 on sheet1 (A was the x-category data)
I copied some cells from Sheet2, right clicked in chart area of chart and
After the copy, I could have left clicked the chart and used Edit | Paste
Special to specify more about the data I want to paste.
Try it and come back with more questions.
Bernard V Liengme
remove caps from email