Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables | Excel Discussion (Misc queries) | |||
Dynamically changing several pivot tables at once | Excel Discussion (Misc queries) | |||
Ho to Delete "Ghost" Pivot Tables | Excel Discussion (Misc queries) | |||
Problems with updating category names in pivot tables | Excel Discussion (Misc queries) | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) |