Pivot Table Problem
 

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

Pivot Table Problem
 

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

Pivot Table Problem
 

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

Pivot Table Problem
 

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

Pivot Table Problem
 

You could use the REPT function to add spaces, but it won't line up
exactly unless you use a non-proportional font. For example:

=D2 & REPT(" ",15-LEN(D2)+3) & B2 & REPT(" ",15-LEN(B2)+3) &C2

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





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Problem
 

Hi John,

=CONCATENATE(D2&" ",B2&" ",C2&" ")

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

You could use a formula like;

=CONCATENATE(D2,REPT(" ",15-LEN(D2)),B2," ",C2)

this pads out the number af spaces according to the length of the part
number in D2. It assumes that entries in column D are not longer than 15
charachters.

Note that you will need to change the font to a fixed-width font like
Courier New to line-up the text nicely using this method.

Ed Ferrero
www.edferrero.com

Pivot Table Problem
 

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

Pivot Table Problem
 

Thanks Ed

I pasted your formula into the spread sheet and although I formatted it with
courier new and the text concatenated it still did not line up.

Thanks for trying. I thought this would be an easy job, at least it is in
Excel 2003, but in 2000 its a pain. I may have to look for an alternative to
over come my pivot table problem, because if Debra Dalgleish and you cant
fix it there is not much hope...lol

Many Thanks for your time


John

"Ed Ferrero" wrote:

Hi John,

=CONCATENATE(D2&" ",B2&" ",C2&" ")

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

You could use a formula like;

=CONCATENATE(D2,REPT(" ",15-LEN(D2)),B2," ",C2)

this pads out the number af spaces according to the length of the part
number in D2. It assumes that entries in column D are not longer than 15
charachters.

Note that you will need to change the font to a fixed-width font like
Courier New to line-up the text nicely using this method.

Ed Ferrero
www.edferrero.com

Pivot Table Problem
 

Many Thanks Debra, however even although I pasted your formula in and it
concatenated the text its still did not line up even after I formatted it
with courier new font.

I may have to look for an alternative solution to my problem.

I much appreciate the time you have given me

Thanks again

John


"Debra Dalgleish" wrote:

You could use the REPT function to add spaces, but it won't line up
exactly unless you use a non-proportional font. For example:

=D2 & REPT(" ",15-LEN(D2)+3) & B2 & REPT(" ",15-LEN(B2)+3) &C2

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





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Problem
 

Dave

I done as you said and formatted the cells to wrap text. The problem with
this is that it puts the 3 combined text into 3 lines in a single cell and
when the pivot table reads the data it displays the text with a small box
between each bit of text and is not easy to read.

Thanks

John


"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

Pivot Table Problem
 

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

Pivot Table Problem
 

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

Pivot Table Problem
 

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

Pivot Table Problem
 

Great! Thanks for reporting that you finally got it working.

John Calder wrote:
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




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html