Thanks to all who have helped.
The pivot table is now working great. I was a bit of a painful excercise in
that Excel 2003 would have done what I wanted in 5 minutes but I could not
have achieved it without your expert help.
Thank you very much
John
"Dave Peterson" wrote:
I'm not sure what you did, but you may want to try the char(10)'s again and
format the cells in the pivottable to wraptext--just to see how it looks.
John Calder wrote:
UPDATE
I applied Debra's formula with Ed's font suggestion and put the text wrap on
as Dave suggested and it seems to be working ok, early days yet but promising
!!!
THANKS EVERYONE !!
"Dave Peterson" wrote:
And then format that column with a Courier New font--it's a monospaced font, so
things will line up better.
One more option:
=d2&char(10)&b2&char(10)&c2
But format the cells to allow wraptext:
format|cells|alignment tab|check wraptext
(and on the PT, too!)
John Calder wrote:
Debra
Sorry, one more thing. I can concatenate the columns ok but as some of the
data is contains a different amount of digits/letters the final output in the
helper column is all over the place. Is there a way I can concatenate three
columns of data but have each entry "tab space" to the next one?
What I have at present:
Formula:-
=CONCATENATE(D2&" ",B2&" ",C2&" ")
=CONCATENATE(D3&" ",B3&" ",C3&" ")
=CONCATENATE(D4&" ",B4&" ",C4&" ")
Output:-
813-62013 PK08007-D GOLD BACKING COAT
1303MU CH00004-P BONDERITE 1303MU
1303R CH00005-P BONDERITE 1303R
As you can see the outputs are not aligned.
What I would like is to have all three lined up.
813-62013 PK08007-D GOLD BACKING COAT
1303MU CH00004-P BONDERITE 1303MU
1303R CH00005-P BONDERITE 1303R
Hope I have explained it ok
Thanks again
John
"John Calder" wrote:
Debra
Thanks for your prompt response.
Combining some of the columns sounds like a great way of overcoming my
problem.
Thanks a lot !
John
"Debra Dalgleish" wrote:
In the source data, can you create a column to combine the colour, its
code and its reference number?
Then add that combined field to the pivot table, instead of all three
fields.
That may reduce the number of items sufficiently that it will allow you
to create the pivot table in Excel 2000.
The pivot table limits are lower in Excel 2000, and you can find more
information on the Microsoft web site:
http://support.microsoft.com/kb/211517/
John Calder wrote:
Hi
I have created a pivot on a spreadsheet on Excel 2000 running under Win 2000.
I require 6 columns of data in the pivot. The 1st 4 columns go in ok but
there are 2 other data columns that when I try to include them I get an error
message saying "Microsoft Excel cannot make this change because there are too
many row or column items"
I then took the file and put it on a computer running WinXP and Excel 2003.
It all works ok under these versions, so I am assuming that Excel 2000 cannot
handle the data in the same way that Excel 2003 does. So, I have 2 questions,
firstly why do I get the error message in Excel 2000 and secondly is there a
fix that I can get that will allow Excel 2000 to deal with this issue.
The data spread is as follows:
Col 1 = A one of entry (in this case a colour)
Col 2 = A one of entry (in this case a code number unique to the colour)
Col 3 = A one of entry (in this case a reference number unique to the colour)
Col 4 = A batch number that may be more than one relating to the colour
Col 5 = A date received that may be more than one entry related to batch
number
Col 6 = A age of paint number that is relateed to each batch number
Columns 1 - 4 go in fine, but the problem is with columns 5 & 6
Any help is much appreciated
John
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html
--
Dave Peterson
--
Dave Peterson