two rows of dates use in pivot table

mish78

New Member
Joined
Apr 27, 2009
Messages
12
Hi,
I am stumped by this and cannot find any answer.

If I have a table as below. Can I combine the dates in a pivot chart??

<table style="border-collapse: collapse; width: 195pt;" border="0" cellpadding="0" cellspacing="0" width="260"><col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108"> <col style="width: 66pt;" width="88"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" height="20" width="64">case</td> <td style="width: 81pt;" width="108">start</td> <td style="width: 66pt;" width="88">end </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">aaaa</td> <td class="xl63" align="right">04/06/2010</td> <td class="xl63" align="right">11/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">bbb</td> <td class="xl63" align="right">11/06/2010</td> <td class="xl63" align="right">11/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">cccc</td> <td class="xl63" align="right">06/06/2010</td> <td class="xl63" align="right">11/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">dddd</td> <td class="xl63" align="right">07/06/2010</td> <td class="xl63" align="right">12/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">eee</td> <td class="xl63" align="right">08/06/2010</td> <td class="xl63" align="right">13/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ffff</td> <td class="xl63" align="right">11/06/2010</td> <td class="xl63" align="right">13/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ggg</td> <td class="xl63" align="right">10/06/2010</td> <td class="xl63" align="right">13/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">hhh</td> <td class="xl63" align="right">11/06/2010</td> <td class="xl63" align="right">13/06/2010</td> </tr> </tbody></table>

so the pivot chart shows ( or normal chart ).....

<table style="border-collapse: collapse; width: 240pt;" border="0" cellpadding="0" cellspacing="0" width="321"><col style="width: 80pt;" span="3" width="107"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 80pt;" height="15" width="107">date</td> <td class="xl66" style="border-left: medium none; width: 80pt;" width="107">count of cases started</td> <td class="xl66" style="border-left: medium none; width: 80pt;" width="107">count of cases ended</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">04/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">05/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">06/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">07/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">08/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">09/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">10/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">11/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">3</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">13/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">4</td> </tr> </tbody></table>
I have figured out how to do it with lookup and count but would rather use a pivot as there may be alot of data.

Any advice apprecciated

mish
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

harshab6

Active Member
Joined
Oct 1, 2008
Messages
254
hi,
sorry unable to get U.what is that result that you want from the pivot table it would be helpful if you would place a example of the result that you would like to get from pivot table.are you having a problem with chart of table.

regards
harsha
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
I doubt you want to do this, but the only way I could get a Pivot Table to do what you want was to reorganize your data like this...
Excel Workbook
BCD
1startendAll Dates
204/06/201004/06/2010
311/06/201011/06/2010
406/06/201006/06/2010
507/06/201007/06/2010
608/06/201008/06/2010
711/06/201011/06/2010
810/06/201010/06/2010
911/06/201011/06/2010
1011/06/201011/06/2010
1111/06/201011/06/2010
1211/06/201011/06/2010
1312/06/201012/06/2010
1413/06/201013/06/2010
1513/06/201013/06/2010
1613/06/201013/06/2010
1713/06/201013/06/2010
...

Excel Workbook
FGH
2Data
3All DatesCount of startCount of end
404/06/20101
506/06/20101
607/06/20101
708/06/20101
810/06/20101
911/06/201033
1012/06/20101
1113/06/20104
Pivot Table result
 

mish78

New Member
Joined
Apr 27, 2009
Messages
12
AlphaFrog - me too. thats the closest I can get but you are correct I dont want that , I want to merge the dates. must be a way somegow....

harshab6 - i have data in the first table and i want to be able to show it in the format in the second table ( pivot table ).


The data is there so I must be able to do it somehow but it seems impossible
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,052
Messages
5,509,046
Members
408,701
Latest member
daz457

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top