Replies

Here is SubjectCodes.xls. Is it possible to attach 2 files to one post?

Possibly a memory problem, look under Troubleshoot PivotTables, in XL Help. Try creating a PT with only the Addresses column as the database, this may use less resources.

Another, rather inelegant, solution is to sort your database by addresses, then use the formula

=IF(TRIM(D1)=TRIM(D2),1,0) in a blank column, Row 2 to the right of your database (assuming your addresses are in Column D), then fill down to the end of your database. You will now have a 1 against each duplicate address. The TRIM function takes out any excess spaces which might confuse XL.(It may be better to make a TRIMed copy of your addesses first and sort on this).

Kerrie
Make sure your columns all have headings otherwise the PT Wizard will not work. Select the whole of your data, go to Data Menu/Pivot Table Report. At Step 1 select Microsoft Excel list or database, then Next. At Step 2 your data should already be selected, Next. At Step 3 drag Address into the Row area, then drag Address again into the Data area, it should become Count of Address, if not double click on it and select Count, then Ok, Next. At Step 4 decide if you want the result on a new sheet or on an existing sheet (eg beside your data), click Finish.

If you make a Pivot Table with the addresses in the Rows and Count of addresses in the Data section you will get a count of how many times each address appears.

I may have been very lucky, but I have never had trouble with just saving (no deleting, or closing then reopening). It is certainly worth trying just saving first.

You do not have to do all the selecting, clearing, saving, closing, and reopening. Just saving the file does the trick.

I think that you are right Andrew. Thanks to you and Stephen for your help.

Just constructed a PT which did change on refresh, the cells I had given a different font moved down on refresh and the font moved down with them.

Andrew

I tried selecting the whole PT and changing the font (something I have not done before as I usually want to format the borders), and that was retained on a refresh. Then I put a double border around the whole PT and that disappeared on a refresh. Then I formatted single cells with different fonts and they were retained on refresh. Maybe some aspects of formatting are retained and others are not.

Why do you think a single cell’s formatting cannot be retained on refresh? Certainly PTs can change on refresh, but they often are unchanged, and I cannot see why XL should not retain the formatting in this case. And as XL retains a single cell’s font on refresh, why not the border also?

Andrew, I just made a small PT, selected 1 cell, chose format cells and put a heavy border around the cell. XL came up with a message(as expected) that the Enable Selection button was not pressed, cancelled the formatting, pressed ES and reformatted with the heavy border. Worked fine. Pressed the Refresh button and my border disappeared.

Options/Preserve formatting is ticked.

Stephen, My experience is the same as yours, my formatting disappears as soon as I do anything to a PT. In fact I have given up on formatting until the end of a project, when I make a values only copy of the PT and format that.

And here is another oddity I have found with PTs. I have never got the Max data function to work by going into a PT where I have used say Sum for the data, and changing it to Max. If I choose Max when first making the PT it works fine.

Talking XL97 SR2 here.

Thanks Andrew, what you described is my present method. However if you want to hide say the first 90 out of 100 fields, you have to click on the 90 one at a time. This is tedious. Clicking on row 1 and shift+clicking on row 90 doesn’t select all 90 as happens in most other MS products. I was hoping someone knew a way to do this.

Thanks Legare, I had looked there but hadn’t worked out how to use it.

My reply to Mouse right click menu from a cell (01/01/07) may be helpful.

Question. How do you paste links to another reply?

Ctrl-Home will take you to A1, just a hop to B1 then.