Help needed with pivot tables

[danielz]

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

[Dave Peterson]

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

[gjcase]

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

[Dave Peterson]

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

[danielz]

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