Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 4th 06, 03:45 PM posted to microsoft.public.excel.misc
danielz
 
Posts: n/a
Default Help needed with pivot tables


Hi all !

Apologies if this is the wrong place to post this question (or if it is
a stupid question - more likely!) as this is my first post.

I would like to do a pivot table on some data I have - the only problem
is that the data is not completely filled in, ie:

Contact 1 Product 1
Product 2
Product 3
Contact 2 Product 1
...

instead of :

Contact 1 Product 1
Contact 1 Product 2
Contact 1 Product 3
Contact 2 Product 1

Is there any way to get Excel to deal with this elegantly ?

Thank you !

Daniel


--
danielz
------------------------------------------------------------------------
danielz's Profile: http://www.excelforum.com/member.php...o&userid=33151
View this thread: http://www.excelforum.com/showthread...hreadid=529641


  #2   Report Post  
Old April 4th 06, 04:31 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Help needed with pivot tables

If you want to keep it a pivottable, then you're going to have to live with it.

But if you convert it to values (it won't be a pivottable anymore), you can fill
those empty cells using the techniques at Debra Dalgleish's site:
http://www.contextures.com/xlDataEntry02.html

You may want to copy that worksheet, then convert the copy to values (if you'll
still need the pivottable).

danielz wrote:

Hi all !

Apologies if this is the wrong place to post this question (or if it is
a stupid question - more likely!) as this is my first post.

I would like to do a pivot table on some data I have - the only problem
is that the data is not completely filled in, ie:

Contact 1 Product 1
Product 2
Product 3
Contact 2 Product 1
..

instead of :

Contact 1 Product 1
Contact 1 Product 2
Contact 1 Product 3
Contact 2 Product 1

Is there any way to get Excel to deal with this elegantly ?

Thank you !

Daniel

--
danielz
------------------------------------------------------------------------
danielz's Profile: http://www.excelforum.com/member.php...o&userid=33151
View this thread: http://www.excelforum.com/showthread...hreadid=529641


--

Dave Peterson
  #3   Report Post  
Old April 4th 06, 04:44 PM posted to microsoft.public.excel.misc
gjcase
 
Posts: n/a
Default Help needed with pivot tables


If I understand you, your issue is that you should have 2 columns of
data, but in some cases the first column is blank & the second one has
shifted into the first?

There's probably a better way to do this, but I would do the
following:
Assume your data is in columns A & B, starting in row 2 (titles in row
1)

1) In C2, place =IF(ISBLANK(B2),C1,A2) and copy it down.
2) In D2, place =IF(ISBLANK(B2),A2,B2) and copy it down.

You now have 2 new coolumns you can use for a pivot table.

If the data is not such that the 2nd column is blank, you might have to
adjust the formulae (For instance if it contains a space, you could use
=IF(B2=" ",C1, A2)

HTH

---GJC


--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=529641

  #4   Report Post  
Old April 4th 06, 04:52 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Help needed with pivot tables

I read your original post incorrectly. You don't already have the pivottable.

But the techniques at Debra's site will still work for you.

Dave Peterson wrote:

If you want to keep it a pivottable, then you're going to have to live with it.

But if you convert it to values (it won't be a pivottable anymore), you can fill
those empty cells using the techniques at Debra Dalgleish's site:
http://www.contextures.com/xlDataEntry02.html

You may want to copy that worksheet, then convert the copy to values (if you'll
still need the pivottable).

danielz wrote:

Hi all !

Apologies if this is the wrong place to post this question (or if it is
a stupid question - more likely!) as this is my first post.

I would like to do a pivot table on some data I have - the only problem
is that the data is not completely filled in, ie:

Contact 1 Product 1
Product 2
Product 3
Contact 2 Product 1
..

instead of :

Contact 1 Product 1
Contact 1 Product 2
Contact 1 Product 3
Contact 2 Product 1

Is there any way to get Excel to deal with this elegantly ?

Thank you !

Daniel

--
danielz
------------------------------------------------------------------------
danielz's Profile: http://www.excelforum.com/member.php...o&userid=33151
View this thread: http://www.excelforum.com/showthread...hreadid=529641


--

Dave Peterson


--

Dave Peterson
  #5   Report Post  
Old April 5th 06, 10:22 AM posted to microsoft.public.excel.misc
danielz
 
Posts: n/a
Default Help needed with pivot tables


Thank you Dave -- Debra's site was exactly what I needed ! (and yes, I
didnt have a pivot table just yet -- thats what I wanted to generate)
Amazing - I spent over 3 hours yesterday trying to figure out a
solution and you gave it to me :-)

Gjcase, thank you also -- the 2nd column info hasnt shifted to the
first column (but you can still use your technique)

You guys are the best !

Daniel


--
danielz
------------------------------------------------------------------------
danielz's Profile: http://www.excelforum.com/member.php...o&userid=33151
View this thread: http://www.excelforum.com/showthread...hreadid=529641



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Tables venetianjigsaw Excel Discussion (Misc queries) 0 February 13th 06 02:51 PM
Dynamically changing several pivot tables at once Jason Excel Discussion (Misc queries) 3 December 16th 05 03:50 PM
Ho to Delete "Ghost" Pivot Tables needyourhelp Excel Discussion (Misc queries) 3 November 17th 05 10:10 PM
Problems with updating category names in pivot tables Vladimir Excel Discussion (Misc queries) 2 November 3rd 05 12:54 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 01:03 AM


All times are GMT +1. The time now is 01:13 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017